Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Use a max date variable in a where clause

Hi, I have just wasted over half a day on this, hoping someone can help what should so basic I shouldn't even be asking but I cannot make this work.

All I am trying to do is load one CSV dataset, find the minimum date, then load a second dataset up to that Min Date.  End state is to cat them both together.   All fields are identical.

I have tried every variation of brackets and quotes to get the variable to work in the both the Let and the Where clause and it either errors with no error message or simply loads 0 lines from Old_Data.

New_Data:

LOAD Date

FROM (ooxml, embedded labels, table is Sheet1);

let vDataCutOff = Min([Date]);

drop table New_Data;

Old_Data:

LOAD *

FROM (ooxml, embedded labels, table is Sheet1)

where [Date] < '$(vDataCutOff)';

Thank you for your help,

Dave

1 Solution

Accepted Solutions
madhumitha
Creator
Creator

Hello Bob,

I am not very sure if this should be the issue, but could you please try in the below format?

Temp:

Load

min(Date) as minDate

Resident New_Data;

Let vDataCutOff= Date(Peek('minDate', 0, 'Temp'));

DROP Table Temp;

Thanks!

View solution in original post

3 Replies
Anil_Babu_Samineni

Could be format issue, Can you check same variable in text object with Date field?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
madhumitha
Creator
Creator

Hello Bob,

I am not very sure if this should be the issue, but could you please try in the below format?

Temp:

Load

min(Date) as minDate

Resident New_Data;

Let vDataCutOff= Date(Peek('minDate', 0, 'Temp'));

DROP Table Temp;

Thanks!

Anonymous
Not applicable
Author

Thanks muchly , Peek worked.  

New_Data:

LOAD date(min([Date])) as minDate

FROM (ooxml, embedded labels, table is Sheet1);

let vDataCutOff = Peek('minDate',0,'New'_Data);

drop table New_Data;

Old_Data:

LOAD *

FROM (ooxml, embedded labels, table is Sheet1)

where [Date] < '$(vDataCutOff)';