topic Re: Status table from status changes in QlikView

Status table from status changes

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Status table from status changes

MarioCenteno — Mon, 16 Jul 2018 13:57:16 GMT

Try

Pivot table

Re: Status table from status changes

YoussefBelloum — Mon, 16 Jul 2018 13:58:53 GMT

Hi,

like this maybe ?

PFA

Re: Status table from status changes

sunny_talwar — Mon, 16 Jul 2018 14:03:00 GMT

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;

Re: Status table from status changes

Anonymous — Mon, 16 Jul 2018 14:41:53 GMT

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!