topic Re: How to load the table for the last 3 days? in QlikView

How to load the table for the last 3 days?

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

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

Re: How to load the table for the last 3 days?

hector_munoz — Wed, 08 Feb 2017 11:45:50 GMT

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

Re: How to load the table for the last 3 days?

sunny_talwar — Wed, 08 Feb 2017 12:03:13 GMT

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;

Re: How to load the table for the last 3 days?

Anonymous — Wed, 08 Feb 2017 12:27:30 GMT

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.

Re: How to load the table for the last 3 days?

Anonymous — Wed, 08 Feb 2017 12:27:51 GMT

Thanks Sunny

Re: How to load the table for the last 3 days?

vinieme12 — Wed, 08 Feb 2017 12:29:42 GMT

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;

Re: How to load the table for the last 3 days?

tresB — Wed, 08 Feb 2017 12:38:00 GMT

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;