Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I am trying to pick the value from below row. If row 1 value is null/blank then i would like to pick the value from below row.
Find the sample to understand it better. Where ID is Document where Sub id is steps which keeps data sorted. So User should not be picked from another ID.
ID | Sub-ID | User | Result required |
1853 | 6 | ABC | ABC |
1853 | 7 | ||
1853 | 8 | ||
1853 | 9 | ||
1853 | 10 | XYZ | |
1853 | 11 | XYZ | XYZ |
1853 | 12 | ||
1853 | 13 | ||
1853 | 14 | ||
1853 | 15 | ABC | |
1853 | 16 | ABC | ABC |
1853 | 17 | ||
1853 | 18 | ||
1853 | 19 | PQR | |
1853 | 20 | PQR | PQR |
1853 | 21 | ||
1854 | 7 | XYZ | XYZ |
1854 | 8 | ||
1854 | 9 | ||
1854 | 10 | ABC | |
1854 | 11 | ABC | ABC |
Result required column is the result that i am looking for.
Thanks in Advance
Hi,
I have done this using below script
if(len(trim(USER))>1,USER,Peek(USER))
But the issue is it is bringing the value From Above row not from below row. To handle this i sorted the data in Descending order.
Order by ID, Sub-ID DESC;
Getting expected result but need to test the complete data.
Still, welcome for any other suggestions.
If(Len(Trim(User)),User,If(ID=Below(ID),Below(User)))
hope this helps
Marco
Hi Marco,
I would like use this in Script. But solution you have provided is not working in even Visual layer expression.
Thanks,
Hi,
I have done this using below script
if(len(trim(USER))>1,USER,Peek(USER))
But the issue is it is bringing the value From Above row not from below row. To handle this i sorted the data in Descending order.
Order by ID, Sub-ID DESC;
Getting expected result but need to test the complete data.
Still, welcome for any other suggestions.
Hi,
sorry, didn't see that you require a script solution.
The expression however seems to work for me.
glad you found a solution
Marco