Table Field

[ba11]

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

[Cascader · Accepted solution]

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;

[ba11]

Thank you, tried this and it's only returning values for sub ID 0 and null for others.

[ba11]

Also it won't let me ORDER BY at the end of my script.

[Chanty4u]

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;

[ba11]

Took a closer look and made some adjustments, it works fine with the Peek() function, thank you everyone.