Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate a leading record

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;   


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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;   


talk is cheap, supply exceeds demand
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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
Not applicable
Author

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;

Not applicable
Author

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

Not applicable
Author

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