Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

johnhhtp2
Contributor II

SCDType1 Issue

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: SCDType1 Issue

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

     ;

    


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
MVP
MVP

Re: SCDType1 Issue

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

1.png

MVP & Luminary
MVP & Luminary

Re: SCDType1 Issue

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

     ;

    


talk is cheap, supply exceeds demand

View solution in original post

johnhhtp2
Contributor II

Re: SCDType1 Issue

Thanks Massimo and Gysbert for your prompt response...