topic Re: Get previous row value when the value is null in QlikView

Get previous row value when the value is null

Anonymous — Fri, 20 Nov 2015 15:37:07 GMT

Dear Experts,

I would like to copy the previous value when the current row value is null.

I tried the peek/previous functions but it is working only for the first blank row and it doesn't carry that logic to the next blank row.

I have Billing Lines and customers.For few billing lines I don't have the Customer details in which I want to copy the Customer number from the previous billing line.

Since the data is huge,could you please suggest the best way to achive this logic?

Thanks & Regards

Jeba

Re: Get previous row value when the value is null

Mark_Little — Fri, 20 Nov 2015 15:40:54 GMT

Would is your logic you have used?

I would normally,do something like below

IF(RowNo() =1 ,

IF(ISNULL(Value)=-1,0

IF(ISNULL(Value)=-1, Previous(Value))

)

) AS Value,

But that will only work for one step back, if you could have more than one value blank then you need to look at the peek and longer nested if.

Mark

Re: Get previous row value when the value is null

Anonymous — Fri, 20 Nov 2015 15:43:48 GMT

Hi Mark,

The blank rows are dynamic and it will be very difficult to use nested if

Regards

Jeba

Re: Get previous row value when the value is null

Mark_Little — Fri, 20 Nov 2015 15:47:35 GMT

Is you logic the same as mine.

As I was not thinking correctly as this works on loading the table it should basic work as

if the first value is null then 0

then it load the next line if it is null then it will also be 0 unless the value.

So it should always have a value to check back on.

Mark

Re: Get previous row value when the value is null

Peter_Cammaert — Fri, 20 Nov 2015 15:48:15 GMT

Do not use previous(). Use peek() as you would like to propagate the previously propagated value from the resulting table (not the input table) into the next blank field.

LOAD ...

IF (len(trim(Customer)) = 0, peek('Customer'), Customer) AS Customer,

RESIDENT ...

Note that you may have to check for a very first row having a blank Customer.

Best,

Peter

Re: Get previous row value when the value is null

Anonymous — Fri, 20 Nov 2015 15:59:29 GMT

Hi Peter,

Thanks for your reply.

The sample attachment will help you more about my issue.

I want to fill the highlighted yellow rows with the above filled value.The peek function copy only the next row and it doesn't go to the third row where it is blank.Also I am curious it works some time and doesn't work some times.

Kindly help

Thanks & Regads

Jeba

Re: Get previous row value when the value is null

Peter_Cammaert — Fri, 20 Nov 2015 16:12:04 GMT

Then why does my code produce the following result?

Re: Get previous row value when the value is null

Anonymous — Fri, 20 Nov 2015 16:14:33 GMT

Hi Peter,

I will try this again and confirm.

I was using isnull logic.was that wrong?

Regards

Jeba

Re: Get previous row value when the value is null

Peter_Cammaert — Fri, 20 Nov 2015 16:16:48 GMT

IsNull() will work for database fields (databases know about the NULL concept) but not for Excel. Excel will return empty strings.

The trick len(trim(Fieldname)) will work in all cases.

Re: Get previous row value when the value is null

thomaslg_wq — Fri, 20 Nov 2015 16:19:11 GMT

Hi,

Maybe you could use the fill transformation step function (don't know if it works with resident load but it does work on table files load).

LOAD [BILLING LINE],

Customer

FROM

sample.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Replace(2, top, StrCnd(null))

));

Hope it helps,

Thomas

Re: Get previous row value when the value is null

Anonymous — Mon, 23 Nov 2015 09:52:05 GMT

Hi Peter,

I tried with large data set.But it doesn't worked.

It is copying only one row and it doesn't go beyond that.i.e If I have 3 blank rows after 1st row its copying the first row value to next row only and not to the second rows and etc..

Is it because of the large data?

Any guidance will be a very big help for me

Regards

Jeba

Re: Get previous row value when the value is null

Peter_Cammaert — Mon, 23 Nov 2015 10:55:42 GMT

No, the number of rows doesn't impact the correct execution of this LOAD statement. A regular LOAD statement executes row-by-row, and since you only look back one row (in the internal table), every evaluation will will cover a maximum of two rows at a time.

I think we can only help you further if you supply the actual script to us.

Peter

PS. Did you try the alternate solution suggested below by Thomas? It will only work when reading an external file, so you may have to temporarily store an internal table into a QVD.

Re: Get previous row value when the value is null

Anonymous — Mon, 23 Nov 2015 11:33:17 GMT

Hi Peter,

Please find the code.

If(len(Trim(SHIPTO))=0,Peek(SHIPTO),SHIPTO) AS %SHIPTO_tmp

Resident VBRK_tmp

order by %BILLITEM

Thanks & Regards

Jeba

Re: Get previous row value when the value is null

Peter_Cammaert — Mon, 23 Nov 2015 11:49:48 GMT

Simple solution:

First order your table,
only then and in a separate LOAD ... RESIDENT statement, use peek() to fill blank rows

ORDER BY and peek() don't work well together in the same LOAD...

Best,

Peter

Re: Get previous row value when the value is null

Anonymous — Mon, 23 Nov 2015 11:54:37 GMT

Hi Peter,

I tried that as well but,its not working.

Please find the script for your kind reference.

Load *,
If(len(Trim(%SHIPTO_tmp))=0,Peek(%SHIPTO_tmp),%SHIPTO_tmp) AS %SHIPTO
FROM $(BUFFERDIRECTORY)VBRK_Temp.qvd(qvd);

Regards

Jeba

Re: Get previous row value when the value is null

thomaslg_wq — Mon, 23 Nov 2015 12:06:00 GMT

Try this:

// I guess peek() needs -1 parameter :

//if order by needed:

TEMP:

Load *
FROM $(BUFFERDIRECTORY)VBRK_Temp.qvd(qvd)

// order by ORDERFIELD

;

REAL:

Load *,
If(len(Trim(%SHIPTO_tmp))=0,Peek(%SHIPTO_tmp,-1),%SHIPTO_tmp) AS %SHIPTO
resident TEMP;

Drop table TEMP;

//if order by not needed

REAL:

Load *,
If(len(Trim(%SHIPTO_tmp))=0,Peek(%SHIPTO_tmp,-1),%SHIPTO_tmp) AS %SHIPTO
resident TEMP;

Re: Get previous row value when the value is null

Anonymous — Mon, 23 Nov 2015 12:10:39 GMT

Hi Thomas,

Could you please give me more information about this logic?

Regards

Jeba

Re: Get previous row value when the value is null

thomaslg_wq — Mon, 23 Nov 2015 12:26:14 GMT

HI,

1 : in script windows, go on table file load,

2 : load your file, but don't apply now -> next,-> enable transformation step

3 : go in fill tab, and choose, -> fill -> target column 2 -> cell condition (is empty) // above

NEXT NEXT FINISH

It will fill empty rows with above value

Re: Get previous row value when the value is null

Peter_Cammaert — Mon, 23 Nov 2015 12:29:57 GMT

Peek() accepts a field name (as a text string), not a field itself. You should use Peek('%SHIPTO_tmp').

Best,

Peter

Re: Get previous row value when the value is null

NareshGuntur — Mon, 23 Nov 2015 12:37:39 GMT

This might help you

Re: Peek back till not Null

Cheers,

Naresh