IF Statements within a Chart Expression

[ryanfoster84]

Hey,

I am trying to create a table with an IF statement for the expression, but it seems qlikview will only allow so many if statements within the expression. One of the tables i am using is stand alone and isn't joined in the main data model hence why i am using set analysis. Below is a snip it of the IF statement:

=IF([Internal Common Name] = ''TEST",
sum(
{$<
[Internal Common Name] = {'TEST'}
>}
[Revenue])
-
sum(
{$<
[Participant Name] = {"TEST"}
>}
[Payment Amount])
,

IF([Internal Common Name] = 'TEST',
sum(
{$<
[Internal Common Name] = {"TEST"}
>}
[Revenue])
-
sum(
{$<
[Participant Name] = {"TEST"}
>}
[Payment Amount])
,
IF([Internal Common Name] = 'TEST',
sum(
{$<
[Internal Common Name] = {"TEST"}
>}
[Revenue])
-
sum(
{$<
[Participant Name] = {"TEST"}
>}
[Payment Amount])
,
IF([Internal Common Name] = 'TEST',
sum(
{$<
[Internal Common Name] = {"TEST"}
>}
[Revenue])
-
sum(
{$<
[Participant Name] = {"TEST"}
>}
[Payment Amount]),

IF([Internal Common Name] = 'TEST',
sum(
{$<
[Internal Common Name] = {"TEST"}
>}
[Revenue])
-
sum(
{$<
[Participant Name] = {"TEST"}
>}
[Payment Amount]),

This goes on for a number of statements  and this is the end of it

,
SUM([Revenue])))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

My issue is i still have another 5 IF statements to add but i get error in the expression when i try to add it. 

Does anyone have a solution I could use to get around this?

[edwin]

is the code just an illustration?  bec if the condition is repeated, if the first is true, the next will not be executed so theres no point repeating it.  also, if the selected value of the field satisfies the if statement, you dont need to add it to your set analysis:

=IF([Internal Common Name] = ''TEST",  <- if this is true, it means TEST is selected
sum({$<[Internal Common Name] = {'TEST'}<- this is not needed anymore

can you post a picture of your data model?  no need for the data.

if there is a pattern to your IF conditions and your formula, there is a way to not have to use IF statements or at the least reduce the IF statements.  a very deep IF structure may be hard to maintain and even if there is no syntax error it will be hard to find logic errors.

[avinashelite]

Hope you have put the TEST as sample test record in all the condition !!

you could try with Pick() and Match ()  like below 

Pick(

match([Internal Common Name] , 'TEST','TEST2','TEST3'),

Sum(Cond1),

Sum(Condi2),

Sum(cond3)

)

here you could had N number of match and which case to take

[ryanfoster84]

Hi the formula works regardless if the first condition is met. You can see in the first table the column called Revenue is the formula and the second is just the field revenue that is used as part of the set analysis. The second table is the payment amount which is taken away from Sum(revenue) in the first table

So Firm 1 is as follows:

1,562,369 - (-29901) = 1,592,270

Firm 2

-22,745 - (-24000) = 1,255

[edwin]

attaching a picture of your data model would give anyone looking at this a better picture of your issue.  i can only guess that you have two tables (one with revenue and another with payments and you are using the if statements to link them.  and i say this as a big guess as you may not be allowed to show us the data model and not understanding your level of understanding of how qlikview does associations.

if so, qlikview actually does this for you, you must find a way to link these two tables together.  simplest way is to add a new column (same name in both tables).  this associates internal common name to participant name so you dont need the if statements.

[ryanfoster84]

Here is the datamodel:

the issue is there is no relationship between internal common name and participant hence why i was doing the if statement. I have joined the participant to the fact table on year month as payments are monthly

[avinashelite]

Hi @ryanfoster84 

Did you tried with Pick and match function ?

[ryanfoster84 · Accepted solution]

Hi yes i have just tried it and found it worked:

=alt(Pick(match([Internal Common Name],'FIRM1'), Sum(Revenue)) -

sum(
{$<
[Participant Name] = {"FIRM1"}
>}
[Payment Amount])

,

Pick(match([Internal Common Name],'FIRM2'), Sum(Revenue)) -

sum(
{$<
[Participant Name] = {"FIRM2"}
>}
[Payment Amount])

,

Pick(match([Internal Common Name],'FIRM3'), Sum(Revenue)) -

sum(
{$<
[Participant Name] = {"FIRM3"}
>}
[Payment Amount])

,

Pick(match([Internal Common Name],'FIRM4'), Sum(Revenue)) -

sum(
{$<
[Participant Name] = {"FIRM4"}
>}
[Payment Amount])
,

etc

this goes on for all my cases. thanks for the help

[edwin]

The power of QlikView is in its association engine.  however, you are creating your own association engine by doing cascaded decisions.  when you said "I have joined the participant to the fact table on year month as payments are monthly" you actually just defined a relationship between the INTERNAL... table and the PARTICIPANT LOAD table.  That is with you expression, the relationship is the combination of the name + Year Month.  if you can find a way to create a composite key such that you connect these two tables, then your expression will be reduced to

Sum(Revenue) - sum([Payment Amount])

the reason i suggest this is that you dont want to have to keep modifying your code every time a new participant is introduced.  worse if  a new one is added to your data without anyone knowing.

also, if your data grows, you will be forced to optimize