Load recent 2 days data from excel

[rammuthiah]

I have data for many date, I want to load only recent two days data. I tried loading  max(Date) and max(Date-1). But It is not working.

[surendraj]

may be this

max(date) & ' and ' & date(max(date-1))

--Surendra j

[shraddha_g · Accepted solution]

Try

MaxDateTable:

Load Max(DateField) as MaxDate

From .....xlsx];

Let vMaxDate = num(peek('MaxDate',0,'MaxDateTable'));

Load *

From .....xlsx]

where date(DateField ) >=date($(vMaxDate)-1) and date(DateField ) <=date($(vMaxDate))

[RicardodelaRosaQlik]

Hi,

you can use some functions like date(num(today())-2) or date(num(today())-2,'DD/MM/YYYY') if you need to format,

to compare the date column that you want to use for filter.

In this example I´m using today but you can use any date_column you have in your own table.
Regards,
Ricardo

[ashwinishinde]

Try This,

=DATE(Max((Date)-1))

Thanks & Regards,

Ashwini

[ashwinishinde]

Try This,

=DATE(Max((Date)-1))

Thanks & Regards,

Ashwini

[jonathandienst]

If by "recent", you mean nased on the maximum date in the file:

Raw:

LOAD *

FROM excelfile.xlsx (...)

Max_Date:

LOAD Max(Date) as MaxDate

Resident Raw;

Let vMaxDate = Num(Peek('MaxDate'));

DROP Table Max_Date;

Final:

NoConcatenate

LOAD *

Resident Raw

WHERE Date >= ($(vMaxDate) - 1);

DROP Table Raw;

[jonathandienst]

If "recent" means today and yesterday:

Raw:

LOAD *

FROM excelfile.xlsx (...)

Let vMaxDate = Num(Today());

Final:

NoConcatenate

LOAD *

Resident Raw

WHERE Date >= ($(vMaxDate) - 1);

DROP Table Raw;

Or even this:

Let vMaxDate = Num(Today());

Final:

LOAD *

WHERE Date >= (Num(Today()) - 1);

LOAD *

FROM excelfile.xlsx (...)