Search code examples
loadingqliksenseblank-line

Filling Blank values from value from above in Qlik Sense?


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

enter image description here (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?


Solution

  • 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:

    enter image description here