Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Table join with nested if statement

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;

1 Solution

Accepted Solutions
Not applicable

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!

8 Replies
crusader_
Valued Contributor

Re: Table join with nested if statement

Hi, Ruud

Can you provide sample qvw file?

It would be easier to help you.

Andrei

Not applicable

Re: Table join with nested if statement

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

Not applicable

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]

crusader_
Valued Contributor

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.

Your StreefhuurPerEPA table making Resident LOAD from tempEPA.

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

Hope that helps.

Andrei

Not applicable

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)

Not applicable

Re: Table join with nested if statement

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

Not applicable

Re: Table join with nested if statement

See attached

Not applicable

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!