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

    Table join with nested if statement

      Hi all,


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


      Load VHE_ID,



      [Vastgoed kenmerk.xlsx]


      left join


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








        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




      AS [StreefhuurPerEPA]

      Resident tempEPA;

      drop table tempEPA;