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

    Table join with nested if statement

    Ruud Cruchten

      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;