Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sample Data Model:
Date | Col 1 | Col 2 | Value |
---|---|---|---|
10/03/2015 | XYZ | 1M | #NA |
10/04/2015 | XYZ | 2M | 40 |
12/04/2015 | XYZ | 3M | 50 |
11/04/2015 | XYZ | 2M | #NA |
How to replace '#NA' with the previous value for rows with same values in Col 1 and Col 2.
Hi lightsection,
I'm again attaching here the qvw.
Also pasting the Script:
Data:
LOAD Date,
[Col 1],
[Col 2],
Value
FROM
[https://community.qlik.com/thread/211032]
(html, codepage is 1252, embedded labels, table is @1);
Out:
NoConcatenate
Load Date,
[Col 1],
[Col 2],
if(Previous([Col 2])=[Col 2] And Value='#NA',Previous(Value),Value) As Value
Resident Data
Order By
[Col 1],
[Col 2];
Drop Table Data;
Not sure I understand, What is the expected output from the sample data provided above?
Can you check the attachment?
saran7de - Can't find the attachment you mentioned.
Hi lightsection,
I'm again attaching here the qvw.
Also pasting the Script:
Data:
LOAD Date,
[Col 1],
[Col 2],
Value
FROM
[https://community.qlik.com/thread/211032]
(html, codepage is 1252, embedded labels, table is @1);
Out:
NoConcatenate
Load Date,
[Col 1],
[Col 2],
if(Previous([Col 2])=[Col 2] And Value='#NA',Previous(Value),Value) As Value
Resident Data
Order By
[Col 1],
[Col 2];
Drop Table Data;
maybe something like
Alt(Value, Peek(Value)) as Value
regards
Marco
Thanks for your response:-
What happens is when there are several #NA for same col1 and col2 concecutively.
It replaces the first one with a value but not the ones after that
for ex:-
Date | Col | Col2 | value |
1-may | wes | 1x | 20 |
1-may | wes | 2x | 40 |
1-may | wes | 3x | 45 |
2-May | wes | 1x | 2 |
2-May | wes | 2x | #NA |
2-May | wes | 3x | 3 |
3-may | wes | 1x | 3 |
3-may | wes | 2x | #NA |
3-may | wes | 3x | 3 |
4-may | wes | 2x | #NA |
It replaces value for below
2-May | wes | 2x | 40 |
But not other similar records
how to replace all others?
Please check:
Data:
LOAD Date,
Col As [Col 1],
Col2 As [Col 2],
value As Value
FROM
[https://community.qlik.com/thread/211032]
(html, codepage is 1252, embedded labels, table is @2);
Out:
NoConcatenate
Load Date,
[Col 1],
[Col 2],
if(Previous([Col 2])=[Col 2] And Value='#NA',Peek(Value),Value) As Value
Resident Data
Order By
[Col 1],
[Col 2],
Value;
Drop Table Data;