Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Status table from status changes

Hi all,

I have a table in which I record the change of a status of a product (with unique Serial#). The product can have several status entries.

From this table I am trying to retrieve a Inventory table on a certain date.

Table I have:

ProductChangeDateStatus
P00101/01/2018Ordered
P00201/01/2018Ordered
P00301/01/2018Ordered
P00102/01/2018Picking
P00302/01/2018Picking
P00305/01/2018Error
P00106/01/2018Packing
P00309/01/2018Packing
P00210/01/2018Picking
P00211/01/2018Packing
P00112/01/2018Shipped
P00313/01/2018Shipped

Table I want:  

Status
DateOrderedPickingPackingShippedError
01/01/20183
02/01/201812
05/01/201811 1
06/01/20181 1 1
09/01/20181 2
10/01/2018 12
11/01/2018 3
12/01/2018 21
13/01/2018 22

Would anybody have a suggestions on how to create this in a Load or Graph Straight table?

1 Solution

Accepted Solutions
Highlighted

Try this script

Table:

LOAD AutoNumber(Product&ChangeDate) as Key,

*;

LOAD * INLINE [

    Product, ChangeDate, Status

    P001, 01/01/2018, Ordered

    P002, 01/01/2018, Ordered

    P003, 01/01/2018, Ordered

    P001, 02/01/2018, Picking

    P003, 02/01/2018, Picking

    P003, 05/01/2018, Error

    P001, 06/01/2018, Packing

    P003, 09/01/2018, Packing

    P002, 10/01/2018, Picking

    P002, 11/01/2018, Packing

    P001, 12/01/2018, Shipped

    P003, 13/01/2018, Shipped

];


TempTable:

LOAD DISTINCT Product

Resident Table;


Left Join (TempTable)

LOAD DISTINCT ChangeDate

Resident Table;


Concatenate (Table)

LOAD Product,

ChangeDate

Resident TempTable

Where Not Exists(Key, AutoNumber(Product&ChangeDate));


FinalTable:

LOAD Product,

ChangeDate,

If(Product = Previous(Product), If(Len(Trim(Status)) = 0, Peek('Status'), Status), Status) as Status

Resident Table

Order By Product, ChangeDate;


DROP Table Table, TempTable;

Capture.PNG

View solution in original post

4 Replies
Highlighted
Creator III
Creator III

Try

Pivot table

Highlighted
Champion
Champion

Hi,

like this maybe ?

PFA

Highlighted

Try this script

Table:

LOAD AutoNumber(Product&ChangeDate) as Key,

*;

LOAD * INLINE [

    Product, ChangeDate, Status

    P001, 01/01/2018, Ordered

    P002, 01/01/2018, Ordered

    P003, 01/01/2018, Ordered

    P001, 02/01/2018, Picking

    P003, 02/01/2018, Picking

    P003, 05/01/2018, Error

    P001, 06/01/2018, Packing

    P003, 09/01/2018, Packing

    P002, 10/01/2018, Picking

    P002, 11/01/2018, Packing

    P001, 12/01/2018, Shipped

    P003, 13/01/2018, Shipped

];


TempTable:

LOAD DISTINCT Product

Resident Table;


Left Join (TempTable)

LOAD DISTINCT ChangeDate

Resident Table;


Concatenate (Table)

LOAD Product,

ChangeDate

Resident TempTable

Where Not Exists(Key, AutoNumber(Product&ChangeDate));


FinalTable:

LOAD Product,

ChangeDate,

If(Product = Previous(Product), If(Len(Trim(Status)) = 0, Peek('Status'), Status), Status) as Status

Resident Table

Order By Product, ChangeDate;


DROP Table Table, TempTable;

Capture.PNG

View solution in original post

Highlighted
Contributor III
Contributor III

That is working fine Thank you!

I did have to change the date to a ceil(ChangeDate) as ChangeDate as I have 9000 change dates and about 20 status.

Thank you!