Calculate a leading record

[#1]

hi all,

I have attached a sample data set to assist with my question.

Within my data set there are following fields:-

• CUSTOMER_ID
• Ruleset_ID
• Reporting_Order
• CODES

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

[Gysbert_Wassenaar · Accepted solution]

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;   

[marcus_malinow]

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

[Anonymous]

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;

[#5]

actual data is not sorted; the dummy data set was a sorted output form a QV tbl. sorry about that

[#6]

thanks for the reply - this one crashed QV as I have 12m records and only 4 gb of ram on this machine.