topic Mark first instance of Value during Load in QlikView

Mark first instance of Value during Load

Thu, 16 Aug 2012 15:51:35 GMT

I am loading in a data file that contains a receipt number. This number is duplicated against multiple lines. To try to speed up calculations i am looking to populate a new column during the load with a 1 on the first instance of a new receipt number and a 0 on all duplicate lines.

I know I can do this via count(distinct in expressions but want to leverage to speed of sum by utilising 1 and 0 's.

Many thanks

Mark

Re: Mark first instance of Value during Load

swuehl — Thu, 16 Aug 2012 15:58:56 GMT

Maybe like

LOAD

ReceiptNr,

if( exists(ReceiptNr),0,1) as FirstOccurrence,

...

FROM ...;

Re: Mark first instance of Value during Load

Thu, 16 Aug 2012 16:20:28 GMT

Hi Mark,

Your load script should look something like:

TempTable:

LOAD If(exists([Recept ID]),0,1),

ID,

[Recept ID],

Amount

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

This will hopefully produce a table as you desire

Cheers,

Ravi