Greetings,
Before my question, here are my situation.
I am working on an extract script to load daily reports from a server folder.
The logic of loading is to load the latest file until system close date
I have two reports to load: Report A has only one system close date, Report B has two system close dates, Phase 1 and Phase 2.
I named two reports as Source A and Source B and saved them in one master table. 'Year' is year of file dates.
I made a spreadsheet to list each system close date for two sources(reports), then I can use following script to load Source A filed and meet my loading logic.
But I can not use it to load Source B since it has Phase 1 and Phase 2 dates. How to load two files of different dates under one source file?
Can any Qlikview experts help me on this? Thanks so much for any help in advance!!!!!!
LatestDate:
load Source&Year as SourceYear,
num(max([File Date])) as MaxDate
Resident $(vTableNameInput)
Group by Source, Year;
left join (LatestDate)
load Source&Year as SourceYear,
Phase,
[File Date] as EndDate
FROM
[..\FileData\Date_Deadlines.xlsx]
(ooxml, embedded labels, table is Sheet1);
FilterDate:
load SourceYear&Date(if(MaxDate >EndDate, EndDate, MaxDate),'YYYY-MM-DD') as Filter,
Phase
Resident LatestDate;
Drop table LatestDate;
NoConcatenate
Process_Status_Latest:
Load *,
Source&Year&[File Date] as lookup,
Resident $(vTableNameInput)
Where Exists(Filter, Source&Year&[File Date]);
