Qlik Community

App Development

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

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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