Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have a column called 'Work Order' on my table. The values from this column have multiple lines because every work order has work order sub ID associated with them, there's sub ID 0,1,2,3.... There's another column 'Received quantity' with most of the values associated with the sub ID 0 on the work order column, which means there is value 0 on the other rows for sub IDs 1,2.3.... Now I need another column that calculates the material used to make the parts on the work orders using the received quantity value for calculation, but i want it to use the received quantity from the subID 0 for every sub ID rows and not return 0 or null where received quantity is 0.
i.e
Work_order Received_Quantity Weight Material
105261_0 200 30 6000
105261_1 0 10 2000
105261_2 0 6 1200
105261_3 0 8 1600
I want it to use the received qty value associated with sub ID 0 to calculate for the other sub IDs, at the moment it returns value for only the SUB ID 0 and 0 for the other sub IDs
try something like :
LOAD
Work_Order,
If(Right(Work_Order, 1) = '0', Received_Quantity, Peek('Received_Quantity')) as Received_Quantity,
Weight,
If(Right(Work_Order, 1) = '0', Received_Quantity, Peek('Received_Quantity')) * Weight as Material
FROM
[Table]
ORDER BY Work_Order;
try something like :
LOAD
Work_Order,
If(Right(Work_Order, 1) = '0', Received_Quantity, Peek('Received_Quantity')) as Received_Quantity,
Weight,
If(Right(Work_Order, 1) = '0', Received_Quantity, Peek('Received_Quantity')) * Weight as Material
FROM
[Table]
ORDER BY Work_Order;
Thank you, tried this and it's only returning values for sub ID 0 and null for others.
Also it won't let me ORDER BY at the end of my script.
Try this.Mapping:
Mapping LOAD DISTINCT
Work_order,
Received_Quantity as Received_Quantity_Sub_ID_0
Resident YourTable
Where Sub_ID= 0;
Results:
LOAD *,
Weight * ApplyMap('Mapping', Work_order, 0) as Material
Resident YourTable;
Took a closer look and made some adjustments, it works fine with the Peek() function, thank you everyone.