Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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;
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.
Thanks Sunny
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;
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;