How to fill empty lines

[#1]

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

[avinashelite]

Hi

Try like this in the Expression:

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

[#3]

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

[jonathandienst]

Hi

These may help you:

How to populate a sparsely populated field

Generating Missing Data In QlikView

HTH

Jonathan

[engishfaque]

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

[Anonymous]

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

[#7]

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]

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.

[#9]

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]

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?