Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist
Specialist

Filling in blank records (peek/previous/above)

Please see the below sample data. Sometimes the GOODS_COUNTRY field is empty. If the GOODS_COMMODITY_CODE is the same as the record above only I want to use the above country. That would mean that records 14-17 would be SE however 25 and 26 would remain null as the commodity code above (record 21 is different). PFA the sample data in spreadsheet. Can anyone offer advice please? (What I've done using filters fills in all blanks without taking in to account the commodity code)

Capture.PNG

1 Solution

Accepted Solutions
t_chetirbok
Creator III
Creator III

hi Shane,

LOAD GOODS_REC_ID,

     GOODS_COMMODITY_CODE,

     GOODS_DOCUMENT_REF,

     if(isnull(GOODS_COUNTRY_MANUFACTURED) and Previous(GOODS_COMMODITY_CODE)=GOODS_COMMODITY_CODE,peek('GOODS_COUNTRY_MANUFACTURED'),GOODS_COUNTRY_MANUFACTURED)  as GOODS_COUNTRY_MANUFACTURED

FROM

[TB02_20171219_105916.xls]

(biff, embedded labels, table is Sheet1$);

View solution in original post

6 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Shane,


Didn't quite understand the requirement, why wouldn't rows 15,16 and 25,26 be filled if the condition is the GOODS_COMMODITY_CODE is the same?

Frank_Hartmann
Master II
Master II

maybe like this:

LOAD GOODS_REC_ID,

     GOODS_COMMODITY_CODE,

     GOODS_DOCUMENT_REF,    

     if(isnull(GOODS_COUNTRY_MANUFACTURED), peek('GOODS_COUNTRY_MANUFACTURED', - 1), GOODS_COUNTRY_MANUFACTURED) as GOODS_COUNTRY_MANUFACTURED

FROM

(biff, embedded labels, table is Sheet1$);

hope this helps

shane_spencer
Specialist
Specialist
Author

13-17 are the same Comodity Code so the nulls would get from above.

25 and 26 are different to 21 so would remain null.

shane_spencer
Specialist
Specialist
Author

Hi Frank - that is the same as what I've already got. It does not take in to account the Commodity code so fill is records 25 and 26 as DE when they should be null.

t_chetirbok
Creator III
Creator III

hi Shane,

LOAD GOODS_REC_ID,

     GOODS_COMMODITY_CODE,

     GOODS_DOCUMENT_REF,

     if(isnull(GOODS_COUNTRY_MANUFACTURED) and Previous(GOODS_COMMODITY_CODE)=GOODS_COMMODITY_CODE,peek('GOODS_COUNTRY_MANUFACTURED'),GOODS_COUNTRY_MANUFACTURED)  as GOODS_COUNTRY_MANUFACTURED

FROM

[TB02_20171219_105916.xls]

(biff, embedded labels, table is Sheet1$);

shane_spencer
Specialist
Specialist
Author

for some reason I had to use ='' instead of isnull but otherwise that work a treat