SCDType1 Issue

[Anonymous]

Can somebody help me on this pls.

I have few order ID's where they keep changing their status on daily basis. Want to capture the last time updated record.

Suppose Order ID 14 in Day2 sheet of attached file updated 3 times on same day, my qlikview app refresh is also 3 times per day..so

I should capture only the last time  it got updated for that day and should erase the other 2 records which updated that day.

[maxgro]

Directory;

FOR Each d in 'Day1', 'Day2'

  tmp:

  LOAD

  rowno() as ID,

  '$(d)' as Day,

  Order_ID,

     Track_Location,

     Delivered_Status,

     Priority,

     Warehouse_ID,

     Ordered_Date,

     Track_UpdatedDate

  FROM SCD.xlsx

  (ooxml, embedded labels, table is $(d));

  T:

  NoConcatenate LOAD

  *

  Resident tmp

  Where Order_ID <> Peek('Order_ID')

Order By Order_ID, Track_UpdatedDate desc, ID desc;

  STORE T into T_$(d).qvd (qvd);

  DROP Table T;

  DROP Table tmp;

NEXT

LOAD * from T_Day?.qvd (qvd);

[Gysbert_Wassenaar · Accepted solution]

Something like this should do the trick:

Data:

LOAD   

     Order_ID,

     Track_Location,

     Delivered_Status,

     Priority Warehouse_ID,

     Ordered_Date,

     Track_UpdatedDate,

     Date(Floor(Track_UpdatedDate)) as Track_UpdatedDay

FROM

     SCD.xlsx (ooxml, embedded labels, table is Day2)

     ;

RIGHT JOIN (Data)

LOAD

     Order_ID,

     Track_UpdatedDay,

     Max(Track_UpdatedDate) as Track_UpdatedDate

RESIDENT

     Data

GROUP BY

     Order_ID,

     Track_UpdatedDay

     ;

[Anonymous]

Thanks Massimo and Gysbert for your prompt response...