How to load the table for the last 3 days?

[Anonymous]

Hi All,

I have a requirement to load a table with the last 3 dates available in the same. How can i restrict the table by using the where clause.

Attaching a sample qvw and data for better understanding.

My intention is to use something like this.

LOAD date,

     value

FROM

[Sample_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

where date>max(date)-4;

but here max function is not working. also i don't want to use the date explicitly in the script(date>1/28/2015)

Thanks

[hector_munoz]

Hi Jasmal,

A possible solution would be:

DATES1:

LOAD DISTINCT date AS date1

FROM Sample_Data.xlsx (ooxml, embedded labels, table is Sheet1);

DATES2:

LOAD date1 AS date2

RESIDENT DATES1

ORDER BY date1 DESC;

LET vsLasDate3 = Peek('date2', 2, 'DATES2');

DATES:

LOAD date,

  value

FROM Sample_Data.xlsx (ooxml, embedded labels, table is Sheet1)

WHERE date >= '$(vsLasDate3)';

I attach the sample; hope it serves...

Regards,
H

[sunny_talwar · Accepted solution]

Another way

Table:

LOAD date,

    value

FROM

[Sample_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Join (Table)

LOAD Max(date) - 3 as MaxDate

Resident Table;

FinalTable:

LOAD date,

  value

Where Flag = 1;

LOAD date,

  value,

  If(date > MaxDate, 1, 0) as Flag

Resident Table;

DROP Table Table;

[Anonymous]

Hi Hector. Thanks for your quick response. I think you are taking only the distinct values of the dates. but i need to have all the values.

[Anonymous]

Thanks Sunny

[vinieme12]

Or Just this

temp:

LOAD

Date(MinDate + IterNo()) AS date

WHILE MinDate + IterNo() <= MaxDate;

LOAD Max(date)-3 as MinDate,

Max(date) as MaxDate

FROM

(ooxml, embedded labels, table is Sheet1);

Left Keep

FACT:

LOAD date,

     value

FROM

(ooxml, embedded labels, table is Sheet1);

Drop Table temp;

[tresesco]

If the date field is not continuous , you should rather use Ranking like:

Join (Table)

     //LOAD Max(date) - 3 as MaxDate

     LOAD Max(date,3) as MaxDate

Resident Table;

FinalTable:

LOAD date,

  value

Where Flag = 1;

LOAD date,

  value,

// If(date > MaxDate, 1, 0) as Flag

If(date >= MaxDate, 1, 0) as Flag

Resident Table;