Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
PFA.
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
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
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
Ankit,
You need to oder the field as needed and run the script.
use Order by coloumn1,column2... ASC or DESC.
Thanks,
Sampath