Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sebastian_fager
Contributor III
Contributor III

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
sunny_talwar

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

View solution in original post

3 Replies
sunny_talwar

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

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
Contributor III
Contributor III
Author

Of course! Wonderful, thank you a lot!