Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
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.
Thanks
Stuart
You could do it with something like this:
T1:
LOAD CUSTOMER_ID,
Ruleset_ID,
Reporting_Order,
CODES
FROM [comm161210.xls] (biff, embedded labels, header is 1 lines, table is Sheet1$);
JOIN (T1)
LOAD CUSTOMER_ID,
Ruleset_ID,
min(Reporting_Order) as Reporting_Order,
1 AS FLAG
RESIDENT T1
GROUP BY CUSTOMER_ID, Ruleset_ID;
You could do it with something like this:
T1:
LOAD CUSTOMER_ID,
Ruleset_ID,
Reporting_Order,
CODES
FROM [comm161210.xls] (biff, embedded labels, header is 1 lines, table is Sheet1$);
JOIN (T1)
LOAD CUSTOMER_ID,
Ruleset_ID,
min(Reporting_Order) as Reporting_Order,
1 AS FLAG
RESIDENT T1
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
Try this:
Data:
LOAD
CUSTOMER_ID
Ruleset_ID
Reporting_Order
CODES
FROM ...;
LEFT JOIN (Data) LOAD
CUSTOMER_ID
Ruleset_ID
min(Reporting_Order) as SmallestRO
RESIDENT Data
GROUP BY CUSTOMER_ID, Ruleset_ID;
LEFT JOIN (Data) LOAD
CUSTOMER_ID
Ruleset_ID,
if(Reporting_Order= SmallestRO, 1, 0) as FLAG
RESIDENT 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.