Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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.