Use a max date variable in a where clause

[Anonymous]

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

[Anil_Babu_Samineni]

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

[madhumitha · Accepted solution]

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]

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