Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Day, Everyone !
Please help me out to resolve the below problem statement.
I want replace the all null values with above values available in "Frozen Cost" Column
for example: Date Refreshed 4/1/2022 3:33:26 AM that date "Frozen Cost" value is 1273 so I fill the downward null values with 1273 in table. (screen shot below for your reference)
Please help me out as quickly as possible.
Thank you !
Hi,
Try this:
Make resident load to sort data in the correct way
Add a condition to load correct data instead of missing values and use the peek function to load data from the previous row
If(IsNull([Frozen Cost]) and Peek(SKU) = SKU, Peek([Frozen Cost]), [Frozen Cost]) as [Frozen Cost],
DATA:
NoConcatenate
LOAD
DATE,
SKU,
[Pending Cost],
If(IsNull([Frozen Cost]) and Peek(SKU) = SKU, Peek([Frozen Cost]), [Frozen Cost]) as [Frozen Cost],
Resident DATATMP
Order By SKU , DATE DESC;
Result:
Regards,
Vitalii
Good Day @vchuprina
I do really apologies for late reply.
I try the above script but It is not functioning for me.
Below my script that I try for you reference
Final:
LOAD
DATE_REFRESHED,
IF(COST_TYPE='Frozen Standard Cost Type', [USD_MATERIAL_COST]) as [Frozen Cost],
IF(COST_TYPE='Pending Standard Cost Type', [USD_MATERIAL_COST]) as [Pending Cost],
//0 as [Frozen Cost],
//0 as [Current Cost],
// IF(COST_TYPE='Current Standard Cost Type', [USD_MATERIAL_COST]) as [Current Cost],
SKU,
DESCRIPTION,
MODEL,
ORGANIZATION_NAME,
KEY,
//USD_MATERIAL_COST,
COST_TYPE
//join_key
FROM [lib://Shared -Production Control - AWP/BOM/BOM_DATA_QVD.qvd]
(qvd);
DATA:
NoConcatenate
LOAD
DATE_REFRESHED,
SKU,
[Pending Cost],
If(IsNull([Frozen Cost]) and Peek(SKU) = SKU, Peek([Frozen Cost]), [Frozen Cost]) as [Frozen Cost]
Resident final
Order By SKU , DATE_REFRESHED DESC;
Drop Tables final;
Exit Script;
Get Result on Table is:
Please help me out on this again.
Thanks & Regards
Vinayak Rastogi