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

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

mrichman — Wed, 25 Nov 2020 16:16:04 GMT

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!

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

marcus_sommer — Fri, 04 Jan 2019 08:51:06 GMT

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

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

mrichman — Mon, 07 Jan 2019 08:18:04 GMT

Thanks, it worked!