we need to calculate the requried coulmns before moving to next row ,due to all these attributes are highly interdepended on each other while building Model.

[GowtamK]

In below table, all the yelow coulmns need to calucate in row wise. row no.2 depend on all the there yelow coulmns in row 1.there after it need to go to row 3 and so on. please provide any loop related solution.

[APrasad_Tri]

Hi @Gowtam kumar? ,

Window functions can help achieve the desired output, e.g. FILL, PREV, NEXT, etc for row-level calculation. Can you please provide the formula and the required output format? I need more information to understand the calculation of Inbound/Outbound value and Inbound/Outbound Value Adjustment columns.

Best,

Apeksha Prasad

[GowtamK]

Basically, all the orange color columns have input data. we need to calculate the green color columns on row basis. It should calculate the row number 3 before moving to row4

b’coz

·        “Inbound/Outbound Value Adjustment” depends on previous row “unit cost”

·        “unit cost” depends on previous “on-hand inventory value”

·        “on-hand inventory value” depends on “Inbound/Outbound Value Adjustment”

Inbound/Outbound Value Adjustment

Formula in excel:   =IF(AND(B3=0,F2=0),0,IF(AND(F2<0,F3>0),(-F2*E2)+((B3+F2)*(C3/B3)),IF(OR(F2<0,A3="TOUT"),B3*E2,C3)))

In datapre formula:

IF(RecordID == 1, {Inbound/Outbound Value Adjustment}, IF(AND({Transaction Quantity (NOS)} == 0, PREV({Opening Inventory Quantity}, 1) == 0), 0, IF(AND(PREV({Opening Inventory Quantity}, 1) < 0, {Opening Inventory Quantity} > 0), ((PREV({Opening Inventory Quantity}, 1) * -1) * PREV({Unit Cost}, 1)) + ({Transaction Quantity (NOS)} + PREV({Opening Inventory Quantity}, 1) * ABS({Inbound/Outbound Value} / {Transaction Quantity (NOS)})), IF(OR(PREV({Opening Inventory Quantity}, 1) < 0, {Type of Entry (RECPT_SUB_TYP_CD)} = 'TOUT'), {Transaction Quantity (NOS)} * PREV({Unit Cost}, 1), {Inbound/Outbound Value}))))

Unit cost:

In excel formula: =IF(F3=0,E2,IF(AND(F2<0,F3<0),E2,ABS(G3/F3)))Formula in dataprep:

IF(RecordID == 1, {Unit Cost}, IF(RecordID != 1 && {Opening Inventory Quantity} == 0, PREV({Unit Cost}, 1), IF(RecordID != 1 && PREV({Opening Inventory Quantity}, 1) < 0 && {Opening Inventory Quantity} == 0, PREV({Unit Cost}, 1), ABS({On-Hand Inventory Value} / {Opening Inventory Quantity}))))

On-hand inventory value:

Formula in excel: G2+D3

On-hand inventory quantity:

Formula in excel: F2+B3

I'm happy to jump into the call, if you facing any issue to understanding the problem statement.