topic Re: If the value is null or 0, then Previous value based on condition in Qliksense in QlikView

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

abhijith28 — Fri, 15 Nov 2024 23:32:27 GMT

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,

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

PrashantSangle — Tue, 01 Dec 2020 09:45:05 GMT

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

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

abhijith28 — Tue, 01 Dec 2020 09:49:33 GMT

@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

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

PrashantSangle — Tue, 01 Dec 2020 09:52:47 GMT

can you give sample input with required output...

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

abhijith28 — Tue, 01 Dec 2020 10:16:10 GMT

@PrashantSangle

Please find the below input

Expected Output

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

Kushal_Chawda — Tue, 01 Dec 2020 11:33:10 GMT

@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;

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

abhijith28 — Tue, 01 Dec 2020 11:56:06 GMT

@Kushal_Chawda

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

Please find the attached the sample app.

Thanks,

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

PrashantSangle — Tue, 01 Dec 2020 11:59:28 GMT

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

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

abhijith28 — Tue, 01 Dec 2020 12:11:22 GMT

@PrashantSangle

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

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

PrashantSangle — Tue, 01 Dec 2020 12:26:18 GMT

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

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

abhijith28 — Tue, 01 Dec 2020 12:36:47 GMT

@PrashantSangle @Kushal_Chawda

Rule should be dynamic for ID, we cannot assign rules for some ID's.

Tried the below expression in the chart itself.

Can we use above and below function for comparison?

if( len(Value) = 0 and above(sum(Value))>0 and below(sum(Value))>0 , below(sum(Value)), sum(Value))

but its not returning the expected output.

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

Kushal_Chawda — Tue, 01 Dec 2020 13:23:54 GMT

@abhijith28 see the attached

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

abhijith28 — Wed, 02 Dec 2020 06:47:12 GMT

Thanks @Kushal_Chawda for your time and providing the solution😊