topic Re: Table join with nested if statement in QlikView

Table join with nested if statement

Fri, 25 Jul 2014 18:56:28 GMT

Hi all,

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

Load VHE_ID,

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:

Load*,

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

crusader_ — Fri, 25 Jul 2014 19:05:20 GMT

Hi, Ruud

Can you provide sample qvw file?

It would be easier to help you.

Andrei

Re: Table join with nested if statement

Fri, 25 Jul 2014 21:10:21 GMT

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

Re: Table join with nested if statement

Sat, 26 Jul 2014 18:39:11 GMT

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

crusader_ — Sun, 27 Jul 2014 10:09:53 GMT

Hello,

It seems to me that expression is okay.

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

Your StreefhuurPerEPA table making Resident LOAD from tempEPA.

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

Hope that helps.

Andrei

Re: Table join with nested if statement

Sun, 27 Jul 2014 14:24:29 GMT

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

Sun, 27 Jul 2014 15:18:40 GMT

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

Re: Table join with nested if statement

Sun, 27 Jul 2014 15:56:18 GMT

See attached

Re: Table join with nested if statement

Sun, 27 Jul 2014 18:43:38 GMT

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!