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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mrichman
Creator II
Creator II

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

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! 

1 Solution

Accepted Solutions
marcus_sommer

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

View solution in original post

2 Replies
marcus_sommer

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
Creator II
Creator II
Author

Thanks, it worked!