Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jchang_snwl
New Contributor II

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
MVP
MVP

Re: merge rows based on the value of field

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

11 Replies
MVP
MVP

Re: merge rows based on the value of field

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

Re: merge rows based on the value of field

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

jchang_snwl
New Contributor II

Re: merge rows based on the value of field

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


Re: merge rows based on the value of field

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

Re: merge rows based on the value of field

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;

MVP
MVP

Re: merge rows based on the value of field

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;

Re: merge rows based on the value of field

‌you could also try with a generic load

regards

Marco

Re: merge rows based on the value of field

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

Re: merge rows based on the value of field

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

Community Browser