topic Re: How to fill empty lines in QlikView

How to fill empty lines

Mon, 19 Jan 2015 10:04:08 GMT

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

Re: How to fill empty lines

avinashelite — Mon, 19 Jan 2015 10:08:41 GMT

Try like this in the Expression:

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

Re: How to fill empty lines

Mon, 19 Jan 2015 10:16:45 GMT

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

Re: How to fill empty lines

jonathandienst — Mon, 19 Jan 2015 10:25:18 GMT

These may help you:

How to populate a sparsely populated field

Generating Missing Data In QlikView

HTH

Jonathan

Re: How to fill empty lines

engishfaque — Mon, 19 Jan 2015 11:42:46 GMT

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

Re: How to fill empty lines

Anonymous — Mon, 19 Jan 2015 11:52:53 GMT

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

Re: How to fill empty lines

Mon, 19 Jan 2015 14:16:09 GMT

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.

Re: How to fill empty lines

maleksafa — Mon, 19 Jan 2015 14:19:11 GMT

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.

Re: How to fill empty lines

Mon, 19 Jan 2015 14:32:45 GMT

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.

Re: How to fill empty lines

maleksafa — Mon, 19 Jan 2015 14:44:51 GMT

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?

Re: How to fill empty lines

MarcoWedel — Mon, 19 Jan 2015 22:52:23 GMT

Hi,

one solution could be:

LOAD XXX, 
     If(Len(Status_before) or XXX<>Previous(XXX),Status_before,Peek(Status_after)) as Status_before,
     If(Len(Status_after) or XXX<>Previous(XXX),Status_after,Peek(Status_after)) as Status_after,
     Timestamp
INLINE [
    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
];

hope this helps

regards

Marco

Re: How to fill empty lines

Tue, 20 Jan 2015 10:26:24 GMT

Hi everyone,

there must be something wrong.

I have tried (just to see if at least this would work) :

LOAD *,

peek(Status_before) as Status_before2

resident T2;

And I get :

XXX Status_before Status_after Timestamp Status_before2

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

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

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 lili

The peek function give me the previous value but if the previous value is null, I do get a null instead of getting the last not null value

Re: How to fill empty lines

Tue, 20 Jan 2015 10:40:30 GMT

Hi Laura,

please see attached example

hope that helps

Joe

Re: How to fill empty lines

Tue, 20 Jan 2015 15:27:16 GMT

Thank you Joe !!

I played a bit with your application and now I understand better how peek works !

I was able to make my application work

Have a good day

Re: How to fill empty lines

Tue, 20 Jan 2015 16:29:37 GMT

No problem, glad to help