topic Use a max date variable in a where clause in QlikView

Use a max date variable in a where clause

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

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

Re: Use a max date variable in a where clause

Anil_Babu_Samineni — Thu, 09 Nov 2017 05:56:00 GMT

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

Re: Use a max date variable in a where clause

madhumitha — Thu, 09 Nov 2017 05:56:37 GMT

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!

Re: Use a max date variable in a where clause

Anonymous — Thu, 09 Nov 2017 23:23:24 GMT

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)';