Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

shane_spencer
Valued Contributor

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
Valued Contributor

Re: Filling in blank records (peek/previous/above)

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$);

6 Replies
felipedl
Valued Contributor III

Re: Filling in blank records (peek/previous/above)

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
Honored Contributor

Re: Filling in blank records (peek/previous/above)

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
Valued Contributor

Re: Filling in blank records (peek/previous/above)

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
Valued Contributor

Re: Filling in blank records (peek/previous/above)

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
Valued Contributor

Re: Filling in blank records (peek/previous/above)

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
Valued Contributor

Re: Filling in blank records (peek/previous/above)

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