# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
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?

1 Solution

Accepted Solutions
MVP

May be like this:

Table:

ProductID,    Purchase

5001,              50

5002,              100

35001,            200

35002,            300

];

Right Join (Table)

Purchase as PurchaseToPacking

Resident Table

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

3 Replies
MVP

May be like this:

Table:

ProductID,    Purchase

5001,              50

5002,              100

35001,            200

35002,            300

];

Right Join (Table)

Purchase as PurchaseToPacking

Resident Table

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

Not applicable

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

Right(ProductID, 4)

Purchase

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

Purchase to Packing

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

Contributor II
Author

Of course! Wonderful, thank you a lot!

Tags
Community Browser