Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ankit28287
New Contributor III

Expression to Fill missing Values

HI Guys..Gud Morning..

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

InvoiceLine ItemSales_Person_No.Sales_Person_Name
50014958104570Sushaiv Sharad Wadekar
5001495820
50014958303983Sujit S Dixit
5001495840
5001495850
500149586013131Prashant Kumar
5001495870
5001495880
500149589031313Ashish Goel
50014958100
50014958110
50014958111
50014958112

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

Tags (1)
5 Replies
Not applicable

Re: Expression to Fill missing Values

PFA.

Not applicable

Re: Expression to Fill missing Values

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

ankit28287
New Contributor III

Re: Expression to Fill missing Values

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

ankit28287
New Contributor III

Re: Re: Expression to Fill missing Values

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

Not applicable

Re: Expression to Fill missing Values

Ankit,

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

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

Thanks,

Sampath

Community Browser