Merge unique productID to one row

[sebastian_fager]

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?

[sunny_talwar · Accepted solution]

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]

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]

Of course! Wonderful, thank you a lot!