3 Replies Latest reply: Nov 5, 2018 7:09 PM by Fei Xu RSS

    Auto load data based on the max date avail

    Jane Tan

      Hi All,

       

      I would like to only load my data based on the latest As of Date Available, however, I'm facing some problems, Pls help!

       

      LOAD Max(Asofdate)as Asofdate,

           SegmentGroup as SegmentGroup,

           CustomerName as CustomerName,

           AccountNo. as AccNo,

           MaturityDate as MaturityDate,

            Year(MaturityDate)as Year,

          Month(MaturityDate)as Month,

          Week(MaturityDate)as Week,

          if(month(WeekEnd(MaturityDate)=Month(MaturityDate)),

          div(Day(WeekEnd(MaturityDate)),7),

          div(Day(WeekEND(MaturityDate,-1)),7))+1 as WeekNo,

           CurrentBookbalance as CurrBkBal,

           Status as CustStatus,

           RunoffMonth as RunoffMth,

           RunoffamtSGD as RunoffAmt,

           Segment as Segment,

           TransactionNo. as TransNo

      FROM

      [C:\Users\ASUS User\Desktop\MP Fake data.xlsx]

      (ooxml, embedded labels, table is Master);

        • Re: Auto load data based on the max date avail
          Dilip Ranjith

          Please expand what problems you are facing but

          Max function will need a group by clause.

          • Re: Auto load data based on the max date avail
            jerry Ogwa

            You can also partition the transaction number by Asofdate  and select the most recent partition.

            • Re: Auto load data based on the max date avail
              Fei Xu

              MaxAsofdateTable:
              LOAD
                  Max(Asofdate)as MaxAsofdate         
              FROM
              [C:\Users\ASUS User\Desktop\MP Fake data.xlsx]
              (ooxml, embedded labels, table is Master);

               

              let vMaxAsofdate = date(floor(peek('MaxAsofdate',-1,'MaxAsofdateTable')));
              drop table MaxAsofdateTable;

               

              LOAD
                  Asofdate,
                  SegmentGroup as SegmentGroup,
                  CustomerName as CustomerName,
                  AccountNo. as AccNo,
                  MaturityDate as MaturityDate,
                  Year(MaturityDate)as Year,
                  Month(MaturityDate)as Month,
                  Week(MaturityDate)as Week,
                  if(month(WeekEnd(MaturityDate)=Month(MaturityDate)),
                  div(Day(WeekEnd(MaturityDate)),7),
                  div(Day(WeekEND(MaturityDate,-1)),7))+1 as WeekNo,
                  CurrentBookbalance as CurrBkBal,
                  Status as CustStatus,
                  RunoffMonth as RunoffMth,
                  RunoffamtSGD as RunoffAmt,
                  Segment as Segment,
                  TransactionNo. as TransNo          
              FROM
              [C:\Users\ASUS User\Desktop\MP Fake data.xlsx]
              (ooxml, embedded labels, table is Master)
              where date(floor(Asofdate))='$(vMaxAsofdate)';