Expression to Fill missing Values

[Anonymous]

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

[#2]

PFA.

[#3]

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

[Anonymous]

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

[Anonymous]

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

[#6]

Ankit,

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

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

Thanks,

Sampath