Mark first instance of Value during Load

[#1]

Hi

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

[swuehl · Accepted solution]

Maybe like

LOAD

ReceiptNr,

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

...

FROM ...;

[#3]

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