Any solutions on using Where statements based calculated/formatted fields (ie Date)?

[mrichman]

Hi All, 

I have a load statement with a calculated/formatted field, however when adding a where statement the field tells me that the calculated field is unknown. 

Do you guys have any work around? It is part of an incremental reload. - Because both Max_Date vs Where Date cannot be compared, I'm getting all data instead of the one row. 

i.e.

Incremental:
LOAD Client,
Article,
Date
FROM
[Source_QVD]
(qvd);

MaxDate:
LOAD
max(Date(Date,'DD-MM-YYYY')) as Max_Date
Resident Incremental;

LET Last_updated_date = peek('Max_Date',-1,'Last_updated_date');

Concatenate (Incremental)
LOAD Client,
Article,
Date
FROM
[SOURCE_Excel]
(ooxml, embedded labels, table is Sheet1)
Where Date > $(Last_updated_date);

Thanks in advance! 

[marcus_sommer · Accepted solution]

Try it with:

...
MaxDate: LOAD max(Date) as Max_Date Resident Incremental;

LET Last_updated_date = peek('Max_Date',-1,'MaxDate');

...
Where num(Date) > $(Last_updated_date);

- Marcus

[mrichman]

Thanks, it worked!