8 Replies Latest reply: Jul 27, 2014 2:43 PM by Ruud Cruchten

# Table join with nested if statement

Hi all,

I keep getting the syntax error: ')' expected for the following code:

VHE_EPA

FROM

[Vastgoed kenmerk.xlsx]

left join

Load Distinct VHE_ID, //optioneel Distinct weghalen aangezien VHE_ID vaker voorkomt bij HP_MAXHUUR

HP_MAXHUUR

FROM

[Huurprijzen.xlsx]

StreefhuurPerEpa:

if(VHE_EPA='A', (HP_MAXHUUR*\$(NormEPA-A)),

if(VHE_EPA='B', (HP_MAXHUUR*\$(NormEPA-B)),

if(VHE_EPA='C', (HP_MAXHUUR*\$(NormEPA-C)),

if(VHE_EPA='D', (HP_MAXHUUR*\$(NormEPA-D)),

if(VHE_EPA='E', (HP_MAXHUUR*\$(NormEPA-E)),

if(VHE_EPA='F', (HP_MAXHUUR*\$(NormEPA-F)),

if(VHE_EPA='G', (HP_MAXHUUR*\$(NormEPA-G)), (HP_MAXHUUR*\$(Streefnorm_overige))

)))))))

The idea is that a percentage is calculated (NormEPA- has a value between 0-1) of HP_MAXHUUR based on the value of VHE_EPA. So if VHE has EPA A, H_MAXHUUR is multiplied by the corresponding percentage NormEPA-A.

I tried to this if statement in a graph but it didn't work, so I decided to calculate it as a field in the loading script because this would also improve the app UI performance wise.

The reason I use a left join is because EPA is in a differenct excel file than HP_MAXHUUR.

Also noteworthy is that HP_MAXHUUR isn't distinct, meaning that VHE_ID and HP_MAXHUUR have three values in the excel file.

for example:

Year     VHE_ID     HP_MAXHUUR
2012     0001          869

2013     0001          896

2014     0001          865

The table of EPA is:

VHE_ID   VHE_EPA

0001       A

0002       D

etc.

AS [StreefhuurPerEPA]

Resident tempEPA;

drop table tempEPA;

• ###### Re: Table join with nested if statement

Hi, Ruud

Can you provide sample qvw file?

Andrei

• ###### Re: Table join with nested if statement

No I'm sorry, I've got the personal edition, I'll post some screenshots

• ###### Re: Table join with nested if statement

After the tablejoin, the following expression in a graph table gives - as value while the expression seems to be OK:

SUM(if(Energielabel='A', (Maximumhuur_historisch*\$(NormEPA-A)),

if(Energielabel='B', (Maximumhuur_historisch*\$(NormEPA-B)),

if(Energielabel='C', (Maximumhuur_historisch*\$(NormEPA-C)),

if(Energielabel='D', (Maximumhuur_historisch*\$(NormEPA-D)),

if(Energielabel='E', (Maximumhuur_historisch*\$(NormEPA-E)),

if(Energielabel='F', (Maximumhuur_historisch*\$(NormEPA-F)),

if(Energielabel='G', (Maximumhuur_historisch*\$(NormEPA-G)), (Maximumhuur_historisch*\$(Streefnorm_overige))

)))))))

)*12

in the script VHE_EPA is defined as [Energielabel]

HP_MAXHUUR as [Maximumhuur_historisch]

• ###### Re: Table join with nested if statement

Hello,

It seems to me that expression is okay.

From your screenshots I can't find where do you define tempEPA table.

May be the problem is not in joining, but smth else?

Hope that helps.

Andrei

• ###### Re: Table join with nested if statement

I've added some new screenshots, the idea is that in the collumn Streefhuur, Maximumhuur_historisch*corresponding NormEPA is visualised. The expression didn't work so I tried to calculate it in the load script. Energielabel is a field from a different xlsx file than Maximumhuur_historisch (hence the join). I thought that in Qlikview itself it wouldn't be a problem, but somehow it won't show any values. If I could avoid calculations in the load script that would be great, since the expression has to be flexible (The selected Year (Jaar can change and NormEPA is variable)

• ###### Re: Table join with nested if statement

Hi, You can share the qvw file even though you have personal edition.

• ###### Re: Table join with nested if statement

See attached

• ###### Re: Table join with nested if statement

I've solved it! Don't know why it went wrong there, but I deleted all the variables NormEPA en re-created them. Used the same expression and it worked... Strange but thanks for your replies!