topic Re: Nested If...ElseIf statement in a textbox -- possible? in QlikView

Nested If...ElseIf statement in a textbox -- possible?

Thu, 10 Dec 2015 17:32:44 GMT

It's me again

I'm so appreciative of the help that this community has provided me thus far on my initial jouney into QlikView. I currently have a tab that when activated, automatically selects two values for my 'Question' field ('Group' and 'Self'). I have a text box that I want to display text in, but I want it to change depending on what the selection is. I'm going to try to explain as best as I can.

If both 'Group' and 'Self' are selected (which is the default for the tab), I want to display "Total Productivity". If only 'Group' is selected, I want to display "Group Productivity", and if only 'Self' is selected, display "Self Productivity". If neither is selected, display nothing.

I wrote it out as this:

IF Question = 'Group' THEN

IF Question = 'Self' THEN

Text = "Total Productivity"

ELSE

Text = "Group Productivity"

ELSEIF Question = 'Self' THEN

Text = "Self Productivity"

ELSE

Text = blank

But I don't know how to format it for Qlik. I tried this:

=If(Question='Group',If(Question='Self',"Total Productivity","Group Productivity"),If (Question='Self',"Self Productivity","")

But that doesn't seem to work. Any help would be greatly appreciated.

Thanks,

Tim

Re: Nested If...ElseIf statement in a textbox -- possible?

sunny_talwar — Thu, 10 Dec 2015 18:07:44 GMT

Not sure I understand, how can Question = 'Group' and 'Self' at the same time?

Re: Nested If...ElseIf statement in a textbox -- possible?

Kushal_Chawda — Thu, 10 Dec 2015 18:09:52 GMT

If(Question='Group' and Question='Self','Total Productivity',

If(Question='Group','Group Productivity',

If (Question='Self','Self Productivity',' ')))

Re: Nested If...ElseIf statement in a textbox -- possible?

Thu, 10 Dec 2015 18:13:40 GMT

Hi Sunny,

There's about 10 or 11 questions all listed in the Question field. Two of which, are Self: Productivity' and 'Group: Productivity'. So the user can select both of them to get stats related to just those two questions. I'm hoping that makes it clearer.

Thanks,

Tim

Re: Nested If...ElseIf statement in a textbox -- possible?

sunny_talwar — Thu, 10 Dec 2015 18:18:39 GMT

I think looking at the data will be more helpful. I am still having trouble understanding what you are trying to do.

Re: Nested If...ElseIf statement in a textbox -- possible?

Thu, 10 Dec 2015 18:20:02 GMT

Kush,

That works for Self Productivity and Group Productivity, but Total Productivity doesn't display when both are selected. Any suggestions?

Thanks,

Tim

Re: Nested If...ElseIf statement in a textbox -- possible?

sunny_talwar — Thu, 10 Dec 2015 18:23:40 GMT

May be this:

If(SubStringCount(Concat(DISTINCT Question, '|'), 'Group') = 1 and SubStringCount(Concat(DISTINCT Question, '|'), 'Self') = 1, 'Total Productivity', If(Question = 'Group', 'Group Productivity', If(Question = 'Self', 'Self Productivity', ' ')))

Re: Nested If...ElseIf statement in a textbox -- possible?

Kushal_Chawda — Thu, 10 Dec 2015 18:26:36 GMT

try this

=If(GetFieldSelections(Question)='Group, Self','Total Productivity',

If(GetFieldSelections(Question)='Group','Group Productivity',

If (GetFieldSelections(Question)='Self','Self Productivity',' ')))

Re: Nested If...ElseIf statement in a textbox -- possible?

Thu, 10 Dec 2015 18:28:23 GMT

Here's a sample of the data, if that helps:

About the same	Group: Productivity	Asia
About the same	Group: Productivity	Asia
More Productive	Group: Productivity	Asia
More Productive	Group: Productivity	Australia / NZ
About the same	Group: Productivity	US / Canada
Less Productive	Group: Productivity	US / Canada
Less Productive	Group: Productivity	Australia / NZ
About the same	Group: Productivity	Australia / NZ
About the same	Group: Productivity	US / Canada
More Productive	Self: Productivity	Asia
Less Productive	Self: Productivity	Asia
About the same	Self: Productivity	Asia
More Productive	Self: Productivity	Asia
Less Productive	Self: Productivity	Asia
About the same	Self: Productivity	Asia
More Productive	Self: Productivity	Latin America
Less Productive	Self: Productivity	Latin America
About the same	Self: Productivity	Latin America
About the same	Self: Productivity	US / Canada

Re: Nested If...ElseIf statement in a textbox -- possible?

Thu, 10 Dec 2015 18:38:42 GMT

Kush,

This works, except for one minor detail. I have a OnActivateSheet trigger that automatically sets Question to be the two values, Self and Group. Your IF statement works if I manually select Self, Group or Both, but it doesn't work when both are automatically selected by the trigger. If this is as close as we can get, then so be it, but I didn't know if you (or anybody else) has ever seen something like this.

Thanks,

Tim

Re: Nested If...ElseIf statement in a textbox -- possible?

sunny_talwar — Thu, 10 Dec 2015 18:56:19 GMT

I have seen that things selected with triggers sometime comes with ("Text1"|"Text2") in the current selection box. I would suggest creating a text box with GetFieldSelections(Question) and open the app (let the trigger go off) and then sending the text box object to excel. Copy and paste its content from Excel and paste it as an or statement:

=If(GetFieldSelections(Question)='Group, Self' or

GetFieldSelections(Question) = 'NewText', 'Total Productivity',

If(GetFieldSelections(Question)='Group','Group Productivity',

If (GetFieldSelections(Question)='Self','Self Productivity',' ')))

Re: Nested If...ElseIf statement in a textbox -- possible?

Thu, 10 Dec 2015 19:08:11 GMT

Hi, Sunny,

I think I understand what you're saying, but I'm a little bit confused about "Sending the text box object to excel" and "copy and paste its content from excel and paste it as an or statement".

So I'd create a text box with just "GetFieldSelections(Question) in it, which would get assigned the Group and Self questions. I would then send that to Excel? I'm not sure how I would do that. Sorry again for my lack of QV knowledge.

Thanks,

Tim

Re: Nested If...ElseIf statement in a textbox -- possible?

sunny_talwar — Thu, 10 Dec 2015 19:14:25 GMT

Re: Nested If...ElseIf statement in a textbox -- possible?

Thu, 10 Dec 2015 19:25:03 GMT

Sunny,

That did the trick. I was confused -- I thought you meant that the new text box, and the sending to excel were going to always be part of the solution going forward, not just a one-time thing to get what is being stored.

Thanks again for all of the assistance!

Tim

Re: Nested If...ElseIf statement in a textbox -- possible?

sunny_talwar — Thu, 10 Dec 2015 19:27:28 GMT

Hahahaha, no it was just one time thing . I am glad you were finally able to figure it out. Although I would reward Kush's contribution as well because he was the one who came up with the expression. I just fine tuned it.

Thanks,

Sunny

Re: Nested If...ElseIf statement in a textbox -- possible?

Thu, 10 Dec 2015 19:31:18 GMT

I wish I could select two correct answers, but you're right, special thanks to Kush!

Re: Nested If...ElseIf statement in a textbox -- possible?

sunny_talwar — Thu, 10 Dec 2015 19:34:31 GMT

You can always mark Helpful Answers in addition to correct answer. That option is within the Action button on the bottom left of the response.

Best,

Sunny