Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
siva0606
Contributor III
Contributor III

Nested If Condition

Hi All,

CodeCondition 1Condition 2Condition 3
A001Type A  
B001 Type BType B expect I001
C001 Type BType B expect I001
D001 Type BType B expect I001
E001 Type BType B expect I001
F001 Type BType B expect I001
G001 Type BType B expect I001
H001 Type BType B expect I001
I001 Type B 
J001Type A  

 

I have written the following condition to achieve the above results. Since 2nd condition is being satisified, it is not displaying the third condition as expected.

=If(Match(Code,'A001','J001'),'Type A',

If(Not Match(Code,'A001','J001'),'Type B',

If(Not Match(Code,'A001','J001','I001'),'Type B Expect I001')))

I tried using Pick Match and Valuelist too, but could not bring the results as expected.

Kindly help me to get the actual results.

Labels (1)
  • Other

1 Solution

Accepted Solutions
Vegar
MVP
MVP

Is it possible for a Code  to be associated with more than one type? 

Then you could just add the Condition as a new dimension table in your data model. Consider this data model with a two column transaction table and the new Type dimension table.

Vegar_0-1627472642630.png

It will give you an association for each Code to all it Types like this:

Vegar_1-1627472707231.png

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

9 Replies
Vegar
MVP
MVP

Try changing the order of your nested IF. Like this:

=If(Match(Code,'A001','J001'),
    'Type A',
    If( Not Match(Code,'A001','J001','I001'),
        'Type B Expect I001',
        If( Not Match(Code,'A001','J001'),
            'Type B'
        )
    )
)

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Vegar
MVP
MVP

With the given sample data set you could also use another nested if approach.  Try this:

=if(len([Condition 3]), [Condition 3],
    if(len([Condition 2]), [Condition 2],
       if(len([Condition 1]), [Condition 1])))

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
MayilVahanan

HI

I think, you are trying to get 3 fields?

If so, try like below

If(Match(Code,'A001','J001'),'Type A') as Condition1,

If(Not Match(Code,'A001','J001'),'Type B') as Condition2,

If(Not Match(Code,'A001','J001','I001'),'Type B Expect I001') as Condition3

Reason for 3 condition not satisfied in nested if because, mostly condition2 & condition3 are looks same, so it gives condition 2 result.

Thanks & Regards,
Mayil Vahanan R
siva0606
Contributor III
Contributor III
Author

Hi Mayilvahanan,

Sorry not preferring as 3 separate fields , 'Type A' , 'Type B' , 'Type B Except I001' should be aliased as Type

All three Types in a single column as Type.

Best Regards,

Siva

siva0606
Contributor III
Contributor III
Author

Hi Vegar,

I tried changing the order, but it was not working.

 

Best Regards,

Siva

Vegar
MVP
MVP

If you want to do it in the script, then try this.


LOAD Code,
   if(len([Condition 3]), [Condition 3],
      if(len([Condition 2]), [Condition 2],
         if(len([Condition 1]), [Condition 1]))) as Type
Inline [
Code, Condition 1, Condition 2, Condition 3
A001, Type A,      ,            
B001, ,            Type B,      Type B expect I001
C001, ,            Type B,      Type B expect I001
D001, ,            Type B,      Type B expect I001
E001, ,            Type B,      Type B expect I001
F001, ,            Type B,      Type B expect I001
G001, ,            Type B,      Type B expect I001
H001, ,            Type B,      Type B expect I001
I001, ,            Type B,
J001, Type A,      ,
];

 

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
siva0606
Contributor III
Contributor III
Author

Hi , 

By any chance to do this in front end , why because there are many so many joins and concatenations involved in the backend . so thinking it may affect the existing numbers. Kindly let me know

siva0606
Contributor III
Contributor III
Author

Also tried this is in backend, but not providing the expected results , when we select Type B it should show from B001 to I001 , but it actually shows only I001, which is not correct.

Vegar
MVP
MVP

Is it possible for a Code  to be associated with more than one type? 

Then you could just add the Condition as a new dimension table in your data model. Consider this data model with a two column transaction table and the new Type dimension table.

Vegar_0-1627472642630.png

It will give you an association for each Code to all it Types like this:

Vegar_1-1627472707231.png

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post