topic Re: Expression to Fill missing Values in QlikView

Expression to Fill missing Values

Anonymous — Thu, 10 Apr 2014 03:46:09 GMT

HI Guys..Gud Morning..

i have one case, in that i have to fill missing values by previous available values, as -

Invoice	Line Item	Sales_Person_No.	Sales_Person_Name
50014958	10	4570	Sushaiv Sharad Wadekar
50014958	20
50014958	30	3983	Sujit S Dixit
50014958	40
50014958	50
50014958	60	13131	Prashant Kumar
50014958	70
50014958	80
50014958	90	31313	Ashish Goel
50014958	100
50014958	110
50014958	111
50014958	112

Sales Person for line item 20 is missing...than it should be fill by previous line item 10, Similarly.. 40 and 50 should be fill by line item 30 Sales Person, 70 and 80 by 60 and 100,110,111,112 by 90....can anyone suggest code to fill missing value?

please check test attachment...

thanks in advance..

Ankit

Re: Expression to Fill missing Values

Thu, 10 Apr 2014 04:02:45 GMT

PFA.

Re: Expression to Fill missing Values

Thu, 10 Apr 2014 04:33:51 GMT

Hi Ankit,

Replace the load statement in script with the below one, this should do the trick

LOAD Invoice,

[Line Item],

If(IsNull(Sales_Person_No.) = -1, Peek(Sales_Person_No.), Sales_Person_No.) AS Sales_Person_No.,

If(IsNull(Sales_Person_Name) = -1, Peek(Sales_Person_Name), Sales_Person_Name) AS Sales_Person_Name,

[To Be],

[To Be1]

FROM

(ooxml, embedded labels, table is Sheet1);

Thanks,
Jyotsna

Re: Expression to Fill missing Values

Anonymous — Thu, 10 Apr 2014 10:21:49 GMT

Hi,

Thanks for your replies..

i am getting issue in achieving answer when i am using loading it as excel it work..

but when load qvd i am getting issue..

one sample qvd and qvw is attached..

please if you can help..

thank in advance..

ankit

Re: Re: Expression to Fill missing Values

Anonymous — Thu, 10 Apr 2014 10:22:50 GMT

Hi,

Thanks for your replies..

i am getting issue in achieving answer when i am using loading it as excel it work..

but when load qvd i am getting issue..

one sample qvd and qvw is attached..

please if you can help..

thank in advance..

ankit

Re: Expression to Fill missing Values

Thu, 10 Apr 2014 12:22:27 GMT

Ankit,

You need to oder the field as needed and run the script.

use Order by coloumn1,column2... ASC or DESC.

Thanks,

Sampath