Filling Blank values from value from above in Qlik Sense?

Question

Qlik Sense is a BI tool in which I need to fill the blank values with a value from above

(image from this related thread)

The solution in Qlik View is the following by this thread but in Qlik Sense, it only fills one value below the above value.

if(len(trim("Date"))=0, peek("Date"), "Date") as Date1, 
if("Date"= ' ' or "Date"= '' or  isnull ("Date"),  Previous("Date"),"Date") as Date11,

So how can I fill the blanks with the Dates 01.01.2013 for values 45, 213 and 688; and 02.01.2013 for 3232, 578 and 64421.

Small working example that does not work

Dataaaaa:
Load
    if( len( trim([Date 1]) ) = 0, peek([Date 1]), [Date 1])  as Datee,
    if( len( trim(Dat) ) = 0, peek(Dat), Dat) as DateII
;
Load * Inline [
    Date 1      , Dat   ,   Value11, Value22,
    'Hello'     , 'ha'  ,   20    , 30
    ''          ,       ,   45    , 321,
    ''          ,       ,   213   , 23
    ''          ,       ,   678   , 798
    'No Word'   , 'he'  ,   123   , 123
    ''          ,       ,   3232  , 3232
    ''          ,       ,   578   , 953
    ''          ,       ,   64421 , 7655
];

How does the filling of the blanks work in the Data Load Editor of Qlik Sense?

score 2 · Accepted Answer · answered Sep 06 '17 at 15:12

After running the script below both fields Date and Date1 will be filled. Date1 is just an example how this can be achieved in a new field.

Btw QlikView v12 and Qlik Sense have the same engine in the background so there shouldn't be any difference where the script is running.

Data:
Load
//  Date,
    Value1,
    Value2,
    if( len( trim(Date) ) = 0, peek(Date), Date)  as Date,
    if( len( trim(Date) ) = 0, peek(Date1), Date) as Date1
;
Load * Inline [
    Date        , Value1, Value2,
    '01.01.2013', 20    , 30
    ''          , 45    , 321,
    ''          , 213   , 23
    ''          , 678   , 798
    '02.02.2013', 123   , 123
    ''          , 3232  , 3232
    ''          , 578   , 953
    ''          , 64421 , 7655
];

Result:

What about if you have spaces in the column names? Do you have to escape the spaces or quotes or something else? I can get this working but struggling with spaces in the column names. — hhh, Sep 06 '17 at 15:54
I updated the question with a small working example that does not work [here](https://stackoverflow.com/q/46078284/164148), I can get your example working but not slightly modified versions of it. — hhh, Sep 06 '17 at 16:23

score 0 · Answer 2 · answered Sep 06 '17 at 17:18

You can get the non-working example getting work like the below. The peek command refers to the ongoing column, not the inline column, hence the corrections.

Dataaaaa:
Load
    if( len( trim([Date 1]) ) = 0, peek(Datee), [Date 1])  as Datee,
    if( len( trim(Dat) ) = 0, peek(DateII), Dat) as DateII
;
Load * Inline [
    Date 1      , Dat   ,   Value11, Value22,
    'Hello'     , 'ha'  ,   20    , 30
    ''          ,       ,   45    , 321,
    ''          ,       ,   213   , 23
    ''          ,       ,   678   , 798
    'No Word'   , 'he'  ,   123   , 123
    ''          ,       ,   3232  , 3232
    ''          ,       ,   578   , 953
    ''          ,       ,   64421 , 7655
];

Further information in preceding load.

Filling Blank values from value from above in Qlik Sense?

How does the filling of the blanks work in the Data Load Editor of Qlik Sense?

2 Answers2