Auto load data based on the max date avail

[Anonymous]

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

(ooxml, embedded labels, table is Master);

[dplr-rn]

Please expand what problems you are facing but

Max function will need a group by clause.

[jerifortune]

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

[NZFei]

MaxAsofdateTable:
LOAD
    Max(Asofdate)as MaxAsofdate         
FROM

(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

(ooxml, embedded labels, table is Master)
where date(floor(Asofdate))='$(vMaxAsofdate)';

[Anonymous]

Hi there, I have tried this method but unfortunately, it did not manage to load. I have created a calendar instead, thanks for the advice!

[Anonymous]

problem solved, thanks!

[Anonymous]

problem solved, thanks!