Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mrichman
Contributor 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
MVP & Luminary
MVP & Luminary

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

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

2 Replies
MVP & Luminary
MVP & Luminary

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

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
Contributor II

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

Thanks, it worked!