Status table from status changes

[Anonymous]

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:

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

Table I want:  

	Status
Date	Ordered	Picking	Packing	Shipped	Error
01/01/2018	3
02/01/2018	1	2
05/01/2018	1	1			1
06/01/2018	1		1		1
09/01/2018	1		2
10/01/2018		1	2
11/01/2018			3
12/01/2018			2	1
13/01/2018			2	2

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

[MarioCenteno]

Try

Pivot table

[YoussefBelloum]

Hi,

like this maybe ?

PFA

[sunny_talwar · Accepted solution]

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;

[Anonymous]

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!