Sunday 6 December 2015

Issues with Order By, Joins and Previous Function

Key Point 1: If you apply a Join to a table you have previously Sorted using ORDER BY, the table no longer retains the order by, even if for example it's a left join which doesn't add any extra rows, just an extra field at the end.

It makes sense in hindsight, but did cause me confusion and a lot of time to resolve.

Key Point 2: Previous function looks at the previous record that was passed to the Load statement from the source - even if the previous function failed a Where clause and was not processed.



For one of my applications we have customer forecasts which look something like this:

So Customer, Date and Quantity.  In the Qlikview application the forecast needs to be spread across the days between each forecast.  So the 1st Nov for Customer abc should be spread evenly across the 2nd, 3rd, 4th, 5th and 6th November (as 1st and 7th) are weekends.

I've had a QVW script which does this by first sorting the table by Customer and Forecast Date descending (last forecast to first).

Then I have a for loop which runs 0 to 30.  For each record it checks if the Forecast Date + Counter is less than the Previous(Forecast Date).  If it is a "Forecast Spread Date" is set to be Forecast Date + Counter, otherwise Null.  The Nulls are later deleted.  There is special rule for first (ie. last forecast date) record per customer which spreads over 7 days.

So for customer abc, the first date to process is 6/12/2015.  This gets spread over a week.  2nd record is 29/11/2015.  It checks that 29/11/2015 + 0 = 29/11/2015 < 6/12/2015 so keeps that date.  29/11/2015 + 1 also till 29/11/2015 + 7 is not < 6/12/2015 so doesn't keep that date.

This all worked fine, although probably a better way to do it.

Recently a user asked for something extra.  They wanted to count how many forecasts exist in the month for the customer, and use that in some formulas.

To solve between the Order By load and the for loop, I added a [Forecast Month] field to the table and an extra step:

FC_In_Month:
LOAD
  Customer,
  Forecast_Month
  Count(Distinct Month(Forecast Date)) as FC_In_Month
RESIDENT Forecast
GROUP BY Customer, Forecast Month;

LEFT JOIN (Forecast)
  Customer,
  Forecast_Month
  FC_In_Month
RESIDENT FC_In_Month;

This adds an extra field (no new rows though) FC_In_Month.

But after doing this the for loop and date logic didn't work.  I eventually worked out that the Left Join back to the Forecast table had corrupted the sort order that was put in.  To resolve I just resorted again after the join and all okay.  But something I didn't think of at the time when added the extra step.


The other problem came about because there is actually an extra bit of info in the table.

Forecasts can be Firm or NonFirm and can have same date.  The For Loop described above is only interested in NonFirm records - the Firm records are handled elsewhere.

The Load statement that runs within the Loop has a clause Where Type = "NonFirm".

As mentioned above the test is that the Forecast Date + Counter < Previous(Forecast Date).  Problem I had was with the 15th.  Working backwards by date, the first record it came across was the Firm.  The Where clause excluded this record.  It then moved to the Non-Firm record.  The Previous(Forecast Date) was the 15th - the excluded Firm record and so the test failed for the NonFirm record as well as 15/11/2015 + 0 is not < 15/11/2015.

Solution was to create a separate pre-filtered table so that what I fed into the Load statement in the For Loop only has NonFirm.  This worked, but it's not a great solution.

Both of these make sense when I think about them - even obvious.  But it was hard to see why it went wrong and hard to find.  The Previous issue had been in the code and although not common in the data was actually presenting incorrect data to end users.  Was only when testing my before and after that I noticed.


FINAL NOTE: "order by is a clause used for sorting the records of a resident table before they are processed by the load statement".  Never chased down that rule that will the input table to the load statement before processing.  Previously I've often sorted the table earlier or created an interim table_SORTED version.  Too lazy to check, but it is okay to rely on it in the load statement (where clause notwithstanding).


Wednesday 18 November 2015

Qlikview Management Console - Folder and Document list sorting

Our instance of qlikview has a lot of Documents and folders containing them.  We have setup a structure where each report has a sub folder of it's own with an application, data, config folder etc loosely based on the Qlikview Deployment Framework.

In the management console where setup Qlikview Server to find the folders we list out each report folder as a separate Mounted Folder rather than pointing to a top level.  This allows more customisation of structure in Qlikview.  eg.

We try to name the folders in a meaningful way, eg. Admin_...., Beverage_....., Procurement_.....  A problem we came across when setting this up was that as you add new folders, the simply go to the bottom of the list, rather than being sorted alphabetically.  Finding a relevant folder, either here or in the "Documents" tab is difficult when they run to 150+ reports.

I've not been able to find a way in Qlikview to get it to sort the folder names - at least not a standard method.  But have found the following process to allow it.  NOTE: Only done this on our test server as a check - haven't validated if there are any adverse consequences.  This blog is my instruction manual if / when decide to replicate to production.

a) Folder listings:
Management Console -> System -> Setup -> Qlikview Servers -> our Server -> Folders.  This is the list of folders on disk that contain .qvw files to be referenced.  Our Root Folder is a dummy folder as we are doing everything through Mounted folders.

To re-sort the mounted folder listing I found in this file:

"C:\ProgramData\QlikTech\QlikViewServer\Settings.ini" which controls the settings for Qlikview Server including the Mounted folder list.

Within that list is a row for "Document Mounts" that looks like this:


Each Mounted folder has three fields separated by comma: Mounted Name, the folder on disk and a Browserable (1) or not (0) flag.  A semi-colon seperates each Mounted folder.

I (after taking copies of the file as backup) copied the DocumentMounts= row out to Excel ( which is good for this type of thing ), split into multiple rows and fields.  I then sorted the rows and recreated the string based on the format that came out.

I pasted the string back into the settings.ini file and restarted services.  And it worked, the folders had re-aligned themselves.  Of course when I add the next one, will have to do it all again.


b) Document Listings.

Similar problem with the Documents -> User Documents tab.  I had hoped changing the folders list would copy across to here, but didn't appear to happen - or I didn't wait long enough for it to take effect?

To resort this one went to the QVPR dataset at

"C:\ProgramData\QlikTech\ManagementService\QVPR" folder.  There are a number of xml files here that control the settings (although you can do in a SQL server apparently).  The one of interest was "SourceDocumentFolderResource.xml".

Apart from some settings it lists a series of SourceDocumentFolderResource rows, one for each folder listed.  So within a text editor I simply sorted the rows alphabetically, restarted the Services (twice as first time didn't go so well), and voila, sorted list.

There should be minimal risk with these things as if you break the files, you can simply delete them (or return you previously stored copies) and Qlikview should re-generate them from a default.  The QVPR keeps .bak versions also of the xml.