Table join with nested if statement

[#1]

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;

[crusader_]

Hi, Ruud

Can you provide sample qvw file?

It would be easier to help you.

Andrei

[#3]

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

[#4]

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]

[crusader_]

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

[#6]

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)

[#7]

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

[#8]

See attached

[#9 · Accepted solution]

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!