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

How to load the table for the last 3 days?

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

6 Replies
hector_munoz
Specialist
Specialist

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

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
Not applicable
Author

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
Not applicable
Author

Thanks Sunny

vinieme12
Champion III
Champion III

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
tresesco
MVP
MVP

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;