Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I am stuck on filling the missing values in data load editor.
I have a table that looks like this
%ID_Date_key | amount |
---|---|
1_2021-02-16 | - |
1_2019-10-24 | - |
1_2017-08-05 | - |
1_2007-12-23 | 10 |
2_2019-09-12 | - |
2_2022-01-12 | 70 |
2_2012-09-14 | 20 |
2_2017-06-21 | 30 |
3_2015-04-23 | - |
3_2019-07-21 | - |
3_2017-12-07 | 70 |
3_2006-09-12 | 80 |
3_2021-03-05 | 70 |
For each id, I want to fill the missing values from the next available cell . So the final table should look like this
%ID_Date_key | amount |
---|---|
1_2021-02-16 | 10 |
1_2019-10-24 | 10 |
1_2017-08-05 | 10 |
1_2007-12-23 | 10 |
2_2019-09-12 | 70 |
2_2022-01-12 | 70 |
2_2012-09-14 | 20 |
2_2017-06-21 | 30 |
3_2015-04-23 | 70 |
3_2019-07-21 | 70 |
3_2017-12-07 | 70 |
3_2006-09-12 | 80 |
3_2025-03-05 | 70 |
How to fill the issing values as in the above table?
TIA
Try
tmpData:
Load RecNo() as RecNo,
%ID_Date_key,
SubField(%ID_Date_key,'_',1) as ID,
amount
From <Source> (...);
Data:
Load ID,
%ID_Date_key,
If(IsNum(amount) or ID<>Peek(ID),amount,Peek(amount)) as amount
Resident tmpData
Order By RecNo Desc;
Drop Table tmpData;