topic Re: Calculate a leading record in QlikView

Calculate a leading record

Wed, 22 Apr 2015 13:51:03 GMT

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

Gysbert_Wassenaar — Wed, 22 Apr 2015 13:58:05 GMT

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 — Wed, 22 Apr 2015 13:59:33 GMT

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 — Wed, 22 Apr 2015 14:01:43 GMT

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;

Wed, 22 Apr 2015 15:07:31 GMT

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

Wed, 22 Apr 2015 15:08:22 GMT

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