Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Not applicable

Re: Expression to Fill missing Values

PFA.

Highlighted
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

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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

Highlighted
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