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: 
Anonymous
Not applicable

merge rows based on the value of field

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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';

View solution in original post

11 Replies
swuehl
MVP
MVP

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';

sunny_talwar

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';


Capture.PNG

Anonymous
Not applicable
Author

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 isQV_table.PNG

Not sure what is wrong.


Thanks,

Josh


sunny_talwar

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

sunny_talwar

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;

maxgro
MVP
MVP

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;

MarcoWedel

‌you could also try with a generic load

regards

Marco

MarcoWedel

like this:

QlikCommunity_Thread_204468_Pic1.JPG

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

MarcoWedel

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:

QlikCommunity_Thread_204468_Pic2.JPG

hope this helps

regards

Marco