topic Re: What is wrong in my if? in QlikView

What is wrong in my if?

Tue, 24 May 2016 10:40:53 GMT

Hello,

I am trying to manage to get my IF to work .. The problem is, that it does not really sum my sum() values in table 😕

If I use text-box object and the sum() values without if, then it sum() the values correctly without any problem . But even if I put the IF to text-box, it returns 0.

I am using the code below, I made some changes on the names...

If there is anyone who can help me with this, it would be much appreciated! !

// IF USER HAS NOT MADE ANY SELECTIONS OR SELECTED ALL OF THE 3

if(Count(distinct SourceName)=3,

// IF THERE IS NOT ANY SELECTIONS, WE WILL SUM THE A,B AND C WITH FILTERS

(sum({$<SourceName ={'*'} - {'C'}>} VALUE)) + (sum({<[FILTER1]={'BANANA'}, [FILTER2] = {">$(=-100)<$(=100)"}, SourceName={'C'}>} VALUE))

// IF ONE SOURCE HAS BEEN SELECTED AND IT IS C

if(Count(distinct SourceName)=1, if(WildMatch(lower(GetFieldSelections(SourceName)),'*c*'),

// IF IT IS 1 AND IT IS C

(sum({<[FILTER1]={'BANANA'}, [FILTER2] = {">$(=-100)<$(=100)"}, SourceName={'C'}>} VALUE)),

// IF IT IS 1 AND IT IS NOT C

(sum(VALUE))),

// IF THE SELECTION IS DIFFERENT THAN 3 OR 1 (IN THIS CASE 2)

// IF THE C IS WITHIN SELECTIONS

if(WildMatch(lower(GetFieldSelections(SourceName)),'*c*'),

// IF THE SELECTIONS INCLUDE ALSO A

if(WildMatch(lower(GetFieldSelections(SourceName)),'*a*'),

(sum({<SourceName = {'A'}>} VALUE))

+ (sum({<[FILTER1]={'BANANA'}, [FILTER2] = {">$(=-100)<$(=100)"}, SourceName={'C'}>} VALUE))

// IF THE OTHER SELECTION INCLUDES B

if(WildMatch(lower(GetFieldSelections(SourceName)),'*b*'),

(sum({$<SourceName = {'B'}>} VALUE)) + (sum({<[FILTER1]={'BANANA'}, [FILTER2] = {">$(=-100)<$(=100)"}, SourceName={'C'}>} VALUE)))),

// IF THE C IS NOT INCLUDED IN THE SELECTIONS

(sum(VALUE))

)))

The red part of the code is the one that does not work correctly on my table .. Actually it does the sum() values right on text-box for some reason.

Cheers,

Niko

Re: What is wrong in my if?

Anil_Babu_Samineni — Tue, 24 May 2016 10:44:32 GMT

Please put on red color which expression is getting the problem..

Re: What is wrong in my if?

Tue, 24 May 2016 10:45:32 GMT

Yeah, I was going to do that .. !

Re: What is wrong in my if?

Kushal_Chawda — Tue, 24 May 2016 10:54:03 GMT

You are using lower function but highlighting character in UPPER

if(WildMatch(lower(GetFieldSelections(SourceName)),'*a*'),

(sum({<SourceName = {'A'}>} VALUE))

+ (sum({<[FILTER1]={'BANANA'}, [FILTER2] = {">$(=-100)<$(=100)"}, SourceName={'C'}>} VALUE))

// IF THE OTHER SELECTION INCLUDES B

if(WildMatch(lower(GetFieldSelections(SourceName)),'*b*'),

(sum({$<SourceName = {'B'}>} VALUE)) + (sum({<[FILTER1]={'BANANA'}, [FILTER2] = {">$(=-100)<$(=100)"}, SourceName={'C'}>} VALUE))))

Note: check parenthesis as well

Re: What is wrong in my if?

Tue, 24 May 2016 10:56:31 GMT

Hello,

a very good guess! That was just my mistake when I replaced the actual sourcenames.. They are at lowercase on my code. Good guess tho!

Cheers,

Niko

Re: What is wrong in my if?

Anil_Babu_Samineni — Tue, 24 May 2016 11:02:32 GMT

Niko,

Please confirm me, one thing..

Are you filtering A & B from Source name is after selection / Before Selection / Current Selection ?

If Current Selection you should maintain $ for Current Selection in the Expression..

Re: What is wrong in my if?

Tue, 24 May 2016 11:05:00 GMT

The if-condition should be working after selections, or that I am trying to achieve.

Where you mean I should use the $ then?

Cheers,

Niko

Re: What is wrong in my if?

Anil_Babu_Samineni — Tue, 24 May 2016 11:08:00 GMT

Niko,

Please find below.. Or else please share me sample Application ....

Updated:

if(WildMatch(lower(GetFieldSelections(SourceName)),'*B*'), (sum({$<SourceName = {'B'}>} VALUE)) + (sum({<[FILTER1]={'BANANA'}, [FILTER2] = {">$(=-100)<$(=100)"}, SourceName={'C'}>} VALUE)))

if(WildMatch(lower(GetFieldSelections(SourceName)),'*A*'), (sum({$<SourceName = {'A'}>} VALUE)) + (sum({<[FILTER1]={'BANANA'}, [FILTER2] = {">$(=-100)<$(=100)"}, SourceName={'C'}>} VALUE)))

Re: What is wrong in my if?

rubenmarin — Tue, 24 May 2016 11:08:43 GMT

Hi Niko, I don't see why isn't working. Wildmatch isn't case sensitive but set analysis is case sensitive, it leads to think about something with case in set analysis but if that was the case, the first part of the 'If' wouldn't work either.

To check it it's an 'If' or a 'sum' issue you can try changing each if to return a different number (a simple '1', '2', etc...) to check if the 'if' is going to the expected sentence.

Re: What is wrong in my if?

Tue, 24 May 2016 11:18:10 GMT

I have tried something like that already to see what part of my IF-clause does not work.. Just outputting different strings of the steps and they seem to work.

Actually now when I tested with numbers, the last two steps that are highlighted with red.. Does not work the way I want them to work.

If I put number 1 to the if(WildMatch(lower(GetFieldSelections(SourceName)),'*a*'),

and number 2 to the if(WildMatch(lower(GetFieldSelections(SourceName)),'*b*'),

They return the values to the table when I select the opposite value?

Cheers,

Niko

Re: What is wrong in my if?

Tue, 24 May 2016 11:19:39 GMT

That did not have any influence on to that 😕

And I can't share this workbook with this data, and I do not know if I can re-produce this with the sampledata. Have to take a look on to that on some point.

Thanks anyway!

Cheers,

Niko

Re: What is wrong in my if?

Tue, 24 May 2016 11:53:49 GMT

For some reason when selecting all of the 3 options also, it does only show the sourcename C data.. And when nothing is selected, it shows all the data.

Re: What is wrong in my if?

rubenmarin — Tue, 24 May 2016 12:44:43 GMT

When you say opposite is in both cases? Maybe the first condition is also true because the value wich is true for '*c*' or '*b*' is also true for '*a*'. Putting the '*' will return true if the substring is present in the other values.

If not: Can you upload a simple wich demostrates the issue? It doen't needs to have real data, just sample data simulating your document. (I don't have a QV available right now but I will have in a few hours)

Re: What is wrong in my if?

Tue, 24 May 2016 12:51:14 GMT

Well I did not crack that case, but I made a whole new IF-clause that works (at least now).

I could share that for example tomorrow

Still wondering why that earlier did not work...

Cheers,

Niko