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

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

3 Replies
MVP

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

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)

New Contributor II

## Re: Merge unique productID to one row

Of course! Wonderful, thank you a lot!

Community Browser