Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Product Status Change Timeline Data

Hi Experts,

Here I have attached the excel with sample data. Left hand side table is my input and right hand side is the expected output.

Please guide me through the best approach.

Thanks in advance.

Pramod

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Table:

LOAD tAssetKey,

    tAssetStatus,

    FromDate,

    ToDate,

    OrderKey

FROM

[..\..\Downloads\AssetExcerise.xlsx]

(ooxml, embedded labels, table is Sheet1);

TempTable:

LOAD tAssetKey,

  tAssetStatus,

  FromDate,

  If(tAssetKey = Previous(tAssetKey) and tAssetStatus = Previous(tAssetStatus), Peek('FromDateNew'), FromDate) as FromDateNew,

  ToDate

Resident Table

Order By tAssetKey, FromDate, ToDate;

TempTable1:

LOAD tAssetKey,

  tAssetStatus,

  FromDate,

  FromDateNew,

  ToDate,

  If(tAssetKey = Previous(tAssetKey) and tAssetStatus = Previous(tAssetStatus), Peek('ToDateNew'), ToDate) as ToDateNew

Resident TempTable

Order By tAssetKey, ToDate desc, FromDate desc;

FinalTable:

LOAD DISTINCT

  tAssetKey,

  tAssetStatus,

  FromDateNew as FromDate,

  Date(ToDateNew - 1) as ToDate

Resident TempTable1;

DROP Tables Table, TempTable, TempTable1;

View solution in original post

4 Replies
petter
Partner - Champion III
Partner - Champion III

You can solve it the load script or in a table in the UI of Qlik Sense. What is your requirement?

Not applicable
Author

I want to get the data transformed in load script. How can I achieve this efficiently?

sunny_talwar

May be this:

Table:

LOAD tAssetKey,

    tAssetStatus,

    FromDate,

    ToDate,

    OrderKey

FROM

[..\..\Downloads\AssetExcerise.xlsx]

(ooxml, embedded labels, table is Sheet1);

TempTable:

LOAD tAssetKey,

  tAssetStatus,

  FromDate,

  If(tAssetKey = Previous(tAssetKey) and tAssetStatus = Previous(tAssetStatus), Peek('FromDateNew'), FromDate) as FromDateNew,

  ToDate

Resident Table

Order By tAssetKey, FromDate, ToDate;

TempTable1:

LOAD tAssetKey,

  tAssetStatus,

  FromDate,

  FromDateNew,

  ToDate,

  If(tAssetKey = Previous(tAssetKey) and tAssetStatus = Previous(tAssetStatus), Peek('ToDateNew'), ToDate) as ToDateNew

Resident TempTable

Order By tAssetKey, ToDate desc, FromDate desc;

FinalTable:

LOAD DISTINCT

  tAssetKey,

  tAssetStatus,

  FromDateNew as FromDate,

  Date(ToDateNew - 1) as ToDate

Resident TempTable1;

DROP Tables Table, TempTable, TempTable1;

Not applicable
Author

Thank you.