Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm looking for a better way to merge rows with the same SerialNumber based on the value of "Status".
For example, this is the original table:
SerialNumber | Date | Status |
1 | 01/23/2015 | Inventory |
2 | 07/08/2015 | Inventory |
1 | 01/19/2015 | Ship Out |
3 | 06/15/2015 | Inventory |
4 | 06/15/2015 | Inventory |
5 | 06/21/2015 | Inventory |
3 | 06/18/2015 | Ship Out |
6 | 06/21/2015 | Inventory |
7 | 06/21/2015 | Inventory |
2 | 08/15/2015 | Ship Out |
4 | 06/19/2015 | Ship Out |
5 | 08/25/2015 | Ship Out |
And, I would like to make a table like this:
SerialNumber | Inventory Date | Ship Out Date |
1 | 01/23/2015 | 01/19/2015 |
2 | 07/08/2015 | 08/15/2015 |
3 | 06/15/2015 | 06/18/2015 |
4 | 06/15/2015 | 06/19/2015 |
5 | 06/21/2015 | 08/25/2015 |
6 | 06/21/2015 | |
7 | 06/21/2015 |
Thank in advance.
Best,
Josh
Maybe like
LOAD SerialNumber,
Date as [Inventory Date]
FROM YourTable WHERE Status = 'Inventory';
JOIN
LOAD SerialNumber,
Date as [Ship Out Date]
FROM YourTable WHERE Status = 'Ship Out';
Maybe like
LOAD SerialNumber,
Date as [Inventory Date]
FROM YourTable WHERE Status = 'Inventory';
JOIN
LOAD SerialNumber,
Date as [Ship Out Date]
FROM YourTable WHERE Status = 'Ship Out';
Try this:
Table:
LOAD SerialNumber,
Date as Inventory_Date
FROM
[https://community.qlik.com/thread/204468]
(html, codepage is 1252, embedded labels, table is @1)
Where Status = 'Inventory';
Join (Table)
LOAD SerialNumber,
Date as [Ship Out Date]
FROM
[https://community.qlik.com/thread/204468]
(html, codepage is 1252, embedded labels, table is @1)
Where Status = 'Ship Out';
I just give it a try and somehow the table shows no value in those filed joined afterward.
Here is the code I used.
========================================================
ODBC CONNECT TO SERVER;
source:
LOAD
AS SerialNumber,
Status,
AS CreateDate,
UpdateDate,
Year(UpdateDate) AS UpdateYY,
Month(UpdateDate) AS UpdateMM,
Day(UpdateDate) AS UpdateDD;
SQL SELECT *
FROM "a_table"
WHERE STATUS='FS Inventory' OR Status='Ship Out';
//filter out duplicate records which have same SerialNumber and Satus but UpdateDate
filter:
inner keep
LOAD
SerialNumber,
MAX(UpdateDate) AS UpdateDate
Resident source
Group By SerialNumber;
Drop table filter;
//Here is what I want to merge rows
SN_RFID:
LOAD
SerialNumber,
CreateDate,
UpdateDate AS ShipOutDate,
UpdateYY AS ShipOutYY,
UpdateMM AS ShipOutMM,
UpdateDD AS ShipOutDD
Resident source
WHERE Status='Ship Out';
LEFT JOIN
LOAD
SerialNumber,
CreateDate,
UpdateDate AS FSInvDate,
UpdateYY AS FSInvYY,
UpdateMM AS FSInvMM,
UpdateDD AS FSInvDD
Resident source
WHERE Status='FS Inventory';
Drop table source;
========================================================
But the table I got is
Not sure what is wrong.
Thanks,
Josh
create date is timestamp field and QlikView might be having difficult joining it on it. Can you try removing them from one of the two tables where you are joining
SN_RFID:
LOAD
SerialNumber,
CreateDate,
UpdateDate AS ShipOutDate,
UpdateYY AS ShipOutYY,
UpdateMM AS ShipOutMM,
UpdateDD AS ShipOutDD
Resident source
WHERE Status='Ship Out';
LEFT JOIN
LOAD
SerialNumber,
CreateDate,
UpdateDate AS FSInvDate,
UpdateYY AS FSInvYY,
UpdateMM AS FSInvMM,
UpdateDD AS FSInvDD
Resident source
WHERE Status='FS Inventory';
Drop table source;
Add an exit script after the inner keep and look at the result
Maybe the max(UpdateDate) returns a different type then UpdateDate and the join doesn't work.
Or maybe you have the same max UpdateDate for the same SerialNumber
????? are for missing fields in your script
source:
LOAD
????? AS SerialNumber,
Status,
????? AS CreateDate,
UpdateDate;
SQL SELECT *
FROM "a_table"
WHERE STATUS='FS Inventory' OR Status='Ship Out';
//filter out duplicate records which have same SerialNumber and Satus but UpdateDate
filter:
inner keep
LOAD
SerialNumber,
MAX(UpdateDate) AS UpdateDate
Resident source
Group By SerialNumber;
Drop table filter;
exit script;
you could also try with a generic load
regards
Marco
like this:
table1:
Generic
LOAD SerialNumber,
Status&' Date',
Date
FROM [https://community.qlik.com/thread/204468] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 14))));
hope this helps
regards
Marco
if you're only interested in presenting your data this way, you could as well keep your data model and use a pivot table instead:
hope this helps
regards
Marco