How to fetch previous not null value for a field that contains nulls

[lightsection]

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.

[sunny_talwar]

Not sure I understand, What is the expected output from the sample data provided above?

[Saravanan_Desingh]

Can you check the attachment?

[lightsection]

saran7de‌ - Can't find the attachment you mentioned.

[Saravanan_Desingh · Accepted solution]

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;

[MarcoWedel]

‌maybe something like

Alt(Value, Peek(Value)) as Value

regards

Marco

[lightsection]

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?

[Saravanan_Desingh]

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;