0 Replies Latest reply: Feb 12, 2017 9:05 PM by Stella Yao RSS

    How to load two files of difference dates under one source file

    Stella Yao

      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]);