Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

Not applicable

How to fill empty lines

Hello everyone,

I have a table that looks like this :

XXX         Status_before         Status_after               Timestamp

A                lala                          lili                        01/01/2015 03:00:00

A                   -                           -                           01/01/2015  04:00:00

A                  -                            -                           01/01/2015  05:00:00

A                lili                            lulu                       01/01/2015  05:15:34

B                xaxa                       xixi                        01/01/2015   03:43:35

I would like that the "-" (that represent null values)  to be filled with the previous value of status_after (the table is already sorted).

In that case, I would like the empty lines to be filled with "lili".

It has to be done only if the value of "XXX" is the same, that means if I have

B                xaxa                       xixi                        01/01/2015   03:43:35

C                  -                            -                            01/01/2015   02:00:00

C                 -                            -                              01/01/2015  03:00:00

C                lala                        lili                           01/01/2015    03:23:45

The 2 lines with empty values have to stay like this.

I hope I am clear enough.

Thank you for your help.

Have a good day

Laura

1 Solution

Accepted Solutions
Not applicable

Re: How to fill empty lines

Hi Laura,

please see attached example

hope that helps

Joe

14 Replies
avinashelite
Not applicable

Re: How to fill empty lines

Hi

Try like this in the Expression:

if(Status_before=null() or Status_before='' and XXX=previous(XXX),previous(Status_after))

Not applicable

Re: How to fill empty lines

Hi Laura,

I would look to do populate the values within the script, if your data is sorted correctly as you load it in, then you can combine an if isnull() check with the peek() function to evaluate previous rows when the current row is null and meets your criteria

hope that helps

Joe

jontydkpi
Not applicable

Re: How to fill empty lines

Hi

These may help you:

How to populate a sparsely populated field

Generating Missing Data In QlikView

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
engishfaque
Not applicable

Re: How to fill empty lines

Dear LauraS056,

I would recommended you to do that by using "Edit Script > Table Files > Next > Enable Transformation Step >  Tab Fill > Click on Fill Button Left Side > and Put your condition".

It's proper way which gives you long term solution as well as there are so many others features available which helps you in future just explore them.

Kind regards,

Ishfaque Ahmed

neetha_p
Not applicable

Re: How to fill empty lines

Hi Laura,

Try below code in script:

load XXX,
Status_before,
if(XXX= Previous(XXX) and len(Trim(Status_after)) = 0,Previous(Status_after),Status_after) as Status_after,
Timestamp
FROM

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

Regards

Neetha

Not applicable

Re: How to fill empty lines

Hi everyone,

thank you for your answers, unfortunately, I didn't figure out to make it work.

I have tried :

if(isnull(Status_before) and isnull(Status_after) and previous(XXX)=XXX, peek(Status_after), Status_before) as Status_before,

but it doesn't work   I have also tried with previous instead of peek.

maleksafa
Not applicable

Re: How to fill empty lines

use the enable transformation step when loading the file, and go to Fill tab, here you can put your case by selecting the cell condition and is empty, and in the fill type you can select the behavior, Above to take the above field, below to take the below field from the empty cell.

Not applicable

Re: How to fill empty lines

Hi Malek,

Thank you but I would prefer to do it in the script because I have already done transformations on that table and to do what you suggest, I would need to first store my table into a qvd and then import it again.

maleksafa
Not applicable

Re: How to fill empty lines

at the end the transformation step will create a script in, you can use the same one with some tuning to fit your need, can you share your document?