Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
sebastian_fager
New Contributor II

Merge unique productID to one row

Okay, this is a tricky one.. I think.

ProductID    Purchase

5001              50

5002              100

35001            200

35002            300

35001 and 35002 is raw material to create 5001 and 5002.

So the Purchase for 5001 and 5002 is products on there way to the company and Purchase for 35001 and 35002 is raw material on there way to the packing line. And i want this information on one row.

ProductID    Purchase    PurchaseToPacking

5001              50                200

5002              100              300

The rule to find the raw material is:

if ( len(ProductID) = 5 and left(ProductID,1)=3, sum(Purchase),0)

And now i need to Concat or Aggr somehow?

Tags (4)
1 Solution

Accepted Solutions

Re: Merge unique productID to one row

May be like this:

Table:

LOAD * INLINE [

    ProductID,    Purchase

    5001,              50

    5002,              100

    35001,            200

    35002,            300

];

Right Join (Table)

LOAD Right(ProductID, 4) as ProductID,

  Purchase as PurchaseToPacking

Resident Table

Where Len(ProductID) = 5 and Left(ProductID, 1) = 3;


Capture.PNG

3 Replies

Re: Merge unique productID to one row

May be like this:

Table:

LOAD * INLINE [

    ProductID,    Purchase

    5001,              50

    5002,              100

    35001,            200

    35002,            300

];

Right Join (Table)

LOAD Right(ProductID, 4) as ProductID,

  Purchase as PurchaseToPacking

Resident Table

Where Len(ProductID) = 5 and Left(ProductID, 1) = 3;


Capture.PNG

Not applicable

Re: Merge unique productID to one row

If you want to handle it in the charts you could have your dimension be

Right(ProductID, 4)



and have your expressions be

Purchase

    SUM(IF(ProductID = Right(ProductID, 4), Purchase,0)

Purchase to Packing

    SUM(IF(ProductID = Right(ProductID, 4), 0,Purchase)



sebastian_fager
New Contributor II

Re: Merge unique productID to one row

Of course! Wonderful, thank you a lot!