Discussion Board for collaboration on QlikView Scripting.
I have attached a sample data set to assist with my question.
Within my data set there are following fields:-
For each CUSTOMER_ID and Ruleset_ID a CUSTOMER_ID may have multiple codes.
I want to create a flag which highlights the record that has the smallest number in Reporting_Order.
In the attached sample data set I have also given a dummy answer.
Do I need to use the PEEK function to do this? I dont have much experience with that function yet.
Go to Solution.
You could do it with something like this:
FROM [comm161210.xls] (biff, embedded labels, header is 1 lines, table is Sheet1$);
min(Reporting_Order) as Reporting_Order,
1 AS FLAG
GROUP BY CUSTOMER_ID, Ruleset_ID;
Given that your data is sorted, peek or previous would work just fine.
In your initial LOAD
if(isnull(peek('CUSTOMER_ID', -1)) or peek('CUSTOMER_ID', -1) <> CUSTOMER_ID or peek('Ruleset_ID', -1) <> Ruleset_ID, 1, 0) as FLAG
LEFT JOIN (Data) LOAD CUSTOMER_ID Ruleset_ID min(Reporting_Order) as SmallestRORESIDENT DataGROUP BY CUSTOMER_ID, Ruleset_ID;
LEFT JOIN (Data) LOAD CUSTOMER_ID Ruleset_ID, if(Reporting_Order= SmallestRO, 1, 0) as FLAGRESIDENT Data;
DROP FIELD SmallestRO;
actual data is not sorted; the dummy data set was a sorted output form a QV tbl. sorry about that
thanks for the reply - this one crashed QV as I have 12m records and only 4 gb of ram on this machine.