Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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)
Of course! Wonderful, thank you a lot!