If the value is null or 0, then Previous value based on condition in Qliksense

[abhijith28]

Hi QlikExperts/ @sunny_talwar  @Kushal_Chawda 

I need your help to achieve the below scenario. Tried different use case but not able to produce the expected output.

Data Format (Input)

Expected Output

There are more than 40000 unique ID's. But all 40000 ID's data are not shown, always single "ID" will be selected by default.

In the above Expected output For ID=1, value = '-' (null)  is replaced by 20 but not for Date=1/1/2020 as 1/1/2020 is the starting point and there are no values(>0) Prior to the date 1/1/2020.

For ID=2, (3/2/2020 and 4/2/2020) is replaced by 40.

If the input data for ID=3 in the below format, then the output would be same as input as there are no Value prior to null values.

Please help me on this scenario.

Thanks,

[PrashantSangle]

do you want previous value or next value???

if want previous value then try below 

Table1:

Load ID,Date,if(IsNull(Value) or Value='-',peek(Value),Value) as Value inline [
ID,Date,Value
1, 1/1/2020, -
1, 2/1/2020, 10
1, 3/1/2020, -
1, 4/1/2020, 20
1, 5/1/2020, 30
2, 2/2/2020, 15
2, 3/2/2020, -
2, 4/2/2020, -
2, 5/2/2020, 40
2, 6/2/2020, 30
3, 1/1/2020, -
3, 2/1/2020, -
3, 3/1/2020, -
3, 4/1/2020, 20
3, 5/1/2020, 30
];

Thanks & Regards,

Prashant Sangle

[abhijith28]

@PrashantSangle 

I need next value, but it should follow certain rules.

1. ID=2 values should not replace ID=1 values. Replacing values should be within same ID.

2. For ID=3, there should not be any replacement of values, as there are no null values after the values>0. 

3, 1/1/2020, -
3, 2/1/2020, -
3, 3/1/2020, -
3, 4/1/2020, 20
3, 5/1/2020, 30

[PrashantSangle]

can you give sample input with required output...

[abhijith28]

@PrashantSangle 

Please find the below input

Expected Output

[Kushal_Chawda]

@abhijith28  try below

Data:
LOAD ID,
     Date,
     Value
FROM table;

Final:
LOAD *,
     if(ID=previous(ID) and len(trim(Value))=0,peek(New_Value),Value) as New_Value
resident Data
order by ID, Date;

Drop table Data;

[abhijith28]

@Kushal_Chawda 

Tried the same logic but unfortunately its not returning the expected output.

Please find the attached the sample app.

Thanks,

[PrashantSangle]

try this 

Table1:

Load ID,Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Date,Value inline [

ID,Date,Value
1, 1/1/2020, -
1, 2/1/2020, 10
1, 3/1/2020, -
1, 4/1/2020, 20
1, 5/1/2020, 30
1, 6/1/2020, -
2, 2/2/2020, 15
2, 3/2/2020, -
2, 4/2/2020, -
2, 5/2/2020, 40
2, 6/2/2020, 30
3, 1/1/2020, -
3, 2/1/2020, -
3, 3/1/2020, -
3, 4/1/2020, 20
3, 5/1/2020, 30
];

NoConcatenate
Table:
Load ID,Date,If(ID=3,Value,if(IsNull(Value) or Value = '-', peek(Value),Value)) as Value;
Load *
Resident Table1
order by Date desc;

Drop table Table1;

Regards,

Prashant Sangle

[abhijith28]

@PrashantSangle 

There are more than 40k ID and it cannot be hardcoded ID=3

[PrashantSangle]

but somewhere you have to maintain that for which id you want to apply which rule for some which you don't want to apply rule.

you use match() or if you have less no id for which you have to apply rule then do little modification in that if statement and resolve your query