topic Re: if else in QlikView

if else

bharatkishore — Wed, 23 May 2018 12:11:52 GMT

HI All,

i have two conditions written i.e.

if(Country='Brazil' and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.200,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1))) ,

if (Match(Country,'Russia','Russian Federation','Indonesia') and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.270,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1))) as T1

My first condition(else part not happening) is not working the formula where i have renamed T1 only it is considering ..

Can you please help me where i have missed out.

Thanks,

Bharat

Re: if else

shansundar — Wed, 23 May 2018 13:07:39 GMT

Hi,

Can you little bit elaborate your issue, is this the select part of the load statement in script.

Thanks,

Shan S

Re: if else

bharatkishore — Wed, 23 May 2018 13:31:44 GMT

Hi,

Yes it is in the script..

Re: if else

shansundar — Wed, 23 May 2018 13:41:50 GMT

if your country values are case sensitive if not you can try using mixmatch instead of match.

Can you please give little more detail to understand the issue clearly.

Thanks,

Shan S

Re: if else

bharatkishore — Wed, 23 May 2018 14:23:58 GMT

Hi,

I have two conditions:

if(Country='Brazil' and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.200,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1)))

this is all one condtion. if this condtion is not satisfied then it need to go else i.e

if (Match(Country,'Russia','Russian Federation','Indonesia') and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.270,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1))) as T1

But the problem is the first condition is not working directly it is going to else which is second condition.

Please let me know if you need anything more..

Re: if else

bharatkishore — Wed, 23 May 2018 14:30:18 GMT

stalwar1‌ Sunny bhai if you have time can you please have a look..

Re: if else

sunny_talwar — Wed, 23 May 2018 14:35:24 GMT

can you highlight the part of the expression which you think is not right?

Re: if else

bharatkishore — Wed, 23 May 2018 14:40:08 GMT

Sunny Bhai,

if i can take both the expressions individually they are working fine. But when i need to combine both of them into a single one it is not happening like

if(Country='Brazil' and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.200,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1))),

if (Match(Country,'Russia','Russian Federation','Indonesia') and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.270,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1))) as T1

The first condition is not working directly is going to else which i named as T1..

In Sheet object i can see the full first condition and t1 but together i should get T1..

Hope i am clear .. please let me know sunny bhai if i am not clear

Re: if else

sunny_talwar — Wed, 23 May 2018 14:46:05 GMT

Can you highlight (bold) the condition please

Re: if else

bharatkishore — Wed, 23 May 2018 14:49:08 GMT

Sunny Bhai the first condition:

if(Country='Brazil' and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.200,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1)))

The second condition:

if (Match(Country,'Russia','Russian Federation','Indonesia') and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.270,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1))) as T1

Re: if else

sunny_talwar — Wed, 23 May 2018 14:55:21 GMT

but what is not working Bharat? I don't really know what part of the expression is the problem... that is what I am hoping you can help me find. or share some data which we can run with this and you can tell us what the expected output needs to be based on the data provided...

Re: if else

bharatkishore — Wed, 23 May 2018 14:55:35 GMT

Sunny bhai attached app as well.

Please see the select fields there we will see the condition

Re: if else

sunny_talwar — Wed, 23 May 2018 14:57:27 GMT

Can you also share Consfiltered_NR.qvd and also point out what the issue is?

Re: if else

bharatkishore — Wed, 23 May 2018 15:10:00 GMT

PFA qvd sunny bhai.. and also please find the below image

Here if you see i can see the if condition but it should be in T1 ..May be because am i missing an brace..

Sorry sunny bhai to not ask you correctly

Re: if else

shansundar — Wed, 23 May 2018 15:10:44 GMT

Hi Bharat,

I don't see any comparison for your first condition and second condition, so Qlik will consider it as two columns ideally one column without any name and the second column as T1 and thats the reason your T1 is always showing only the second condition.

Add some comparison to tell Qlik when to consider Condition 1 and when to consider condition 2.

Re: if else

bharatkishore — Wed, 23 May 2018 15:16:05 GMT

I am trying to achieve that one only,

the below one is the first condition:

if(Country='Brazil' and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.200,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1)))

if this is not satisfied then it should go to next condition i.e. the below one

if (Match(Country,'Russia','Russian Federation','Indonesia') and Strategic_Part='Tubes',

if(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055),

if(Strategic_Part<>'Tubes' and not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.270,

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1))) as T1

But it is not considering the first one.. can you please help me how to achieve it

Re: if else

sunny_talwar — Wed, 23 May 2018 15:17:04 GMT

What is T1?... you don't have to apologize for anything.... but I am not sure what exactly are you trying to do here? Can you put your required condition in plan simple words

If country is Brazil and stratgtic_part is Tubes and..... then do this

If .... then

If ... then

etc

Re: if else

sunny_talwar — Wed, 23 May 2018 15:21:03 GMT

Try this

If(Country='Brazil',

If(Strategic_Part='Tubes',

If(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055, [mat con loc cur]*1*-1),

If(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.200, [mat con loc cur]*1*-1)),

If(Match(Country,'Russia','Russian Federation','Indonesia'),

If(Strategic_Part='Tubes',

If(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.055, [mat con loc cur]*1*-1),

If(not IsNull([U-D_NL9s]) and [U-D_NL9s]<>0 and [U-D_NL9s]<>0.01, mat_con_qua*-1*[U-D_NL9s]*1.270, [mat con loc cur]*1*-1)))) as test

Re: if else

bharatkishore — Wed, 23 May 2018 15:31:16 GMT

T1 is the name i have given..

if country = brazil ,and strategic_part is Tubes and and stratgtic_part is Tubes and [U-D_NL9s] not null and [U-D_NL9s] <>0 and [U-D_NL9s] <> 0.01 then [Quantity]*(-1)*[price nl9s U-D]*1.055

else (same condition but only change is strategic_part <> Tubes) , then

if country = brazil ,and strategic_part is Tubes and and stratgtic_part is Tubes and [U-D_NL9s] not null and [U-D_NL9s] <>0 and [U-D_NL9s] <> 0.01 then [Quantity]*(-1)*[price nl9s U-D]*1.200

else

if country =('Russia','Indonesia') ,and strategic_part is Tubes and and strategic_part is Tubes and [U-D_NL9s] not null and [U-D_NL9s] <>0 and [U-D_NL9s] <> 0.01 then [Quantity]*(-1)*[price nl9s U-D]*1.055

else (same condition but only change is strategic_part <> Tubes) , then

if country =('Russia','Indonesia') ,and strategic_part is Tubes and and strategic_part <> Tubes and [U-D_NL9s] not null and [U-D_NL9s] <>0 and [U-D_NL9s] <> 0.01 then [Quantity]*(-1)*[price nl9s U-D]*1.055

as Test..

Like this sunny bhai

Re: if else

bharatkishore — Wed, 23 May 2018 15:42:09 GMT

Thank you Sunny bhai.. one last line i have to add at the end of the formula i.e.

if(IsNull([U-D_NL9s]) or [U-D_NL9s]=0 or [U-D_NL9s]=0.01, [mat con loc cur]*1*-1

if i am adding i am getting error(missing brace) can you tell me where i have to add..