Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
please look at the table:
ID | Data |
---|---|
1 | A |
2 | A |
3 | |
4 | B |
5 | |
6 | B |
7 | |
8 |
This is a dataset with missing values. and I would like to achieve in the script that if the value is empty it automatically fills in the previous value.
So for ID 3, Data should be A (according to ID 2)
for ID 5, Data should be B (according to ID 4)
for ID 7, Data should be B (according to ID 6)
and for ID 8, Data should be B (according to automatically generated ID 7 or ID 6?)
Is this possible to generate in the script? (maybe previous() or peek() statement?)
Thanks
Please use below code;
LOAD ID,
IF(isnull(Data),Peek(DATA),Data) as DATA
FROM
(ooxml, embedded labels, table is Sheet1);
Yes, use either peek('Data') or previous(Data) associated to isnull:
if(isnull(Data), previous(Data), Data) as Data2
I would load first the data in a temp table that I would sort (to be sure of the order of the ID) into another table
Fabrice
Please use below code;
LOAD ID,
IF(isnull(Data),Peek(DATA),Data) as DATA
FROM
(ooxml, embedded labels, table is Sheet1);
Hi Previous will not work ..
Only peek will work like below
if(isnull(Data), Peek(Data2), Data) as Data2
Yes this works. Thanks to both of you.
the isNull part was new to me.
Thanks again
hi there,
find attachment.
regards
Prem
This solution doesn't work if the first record readed contains null.
You could add this in the script before the calculation to use the value of the lower ID with not null Value
-- Calculate the lower ID with not null Value
MinIDWithData:
load min(id) as ID resident Table1 where not IsNull(Data);
let v_MinIDWithData = peek('ID', 0, 'MinIDWithData');
drop table MinIDWithData;
-- Calculate the Value
Aux:
load Data resident Table1 where ID = $(v_MinIDWithData);
let v_aux = peek('Data', 0, 'Aux');
drop table Aux;
load.....
IF(isnull(Data),if(isnull(Peek(DATA), $(v_aux), Peek(DATA)),Data) as DATA
......