Filling Blank values from value from above in Qlik Sense?

2.3k views Asked by At

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?

2

There are 2 answers

2
Stefan Stoichev On BEST ANSWER

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

0
hhh On

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.