13 Replies Latest reply: Oct 13, 2014 7:32 AM by anbu cheliyan RSS

    If statement in load script

    Ruud Cruchten

      Hi,

       

      can anyone help with this error message i keep receiving? I can't seem to figure out what the problem is.

      Error in expression:

      ')' expected

       

      The problem occurs during the execution of the following code:

       

      //Vastgoed kenmerken

      VGKenmerken:

      LOAD VHE_ID,

           VHE_EPA AS [Energielabel],

           IF (CV='J', 'CV', 'HR') AS [Verwarmingseenheid],

           IF(Coll='J', 'JA','Nee') AS [Collectief],

           IF(Meter='J', 'Ja', 'Nee') AS [Meter]

             

      FROM

      [C:\Users\----\Documents\Qlikview\Datasets\Vastgoed kenmerk.xlsx]

      (ooxml, embedded labels, table is [Actueel bezit]);

       

      //Huurprijzen

      Join (VGKenmerken)

      LOAD VHE_ID,

           Jaar,

           HP_NETTOHUUR AS [Nettohuur_historisch],

           HP_MAXHUUR AS [Maximumhuur_historisch],

       

          if(VHE_EPA='A',(SUM(HP_MAXHUUR)*$(Streefnorm_A)-SUM(HP_NETTOHUUR)),

          if(VHE_EPA='B',(SUM(HP_MAXHUUR)*$(Streefnorm_B)-SUM(HP_NETTOHUUR)),

          if(VHE_EPA='C',(SUM(HP_MAXHUUR)*$(Streefnorm_C)-SUM(HP_NETTOHUUR)),

          if(VHE_EPA='D',(SUM(HP_MAXHUUR)*$(Streefnorm_D)-SUM(HP_NETTOHUUR)),

          if(VHE_EPA='E',(SUM(HP_MAXHUUR)*$(Streefnorm_E)-SUM(HP_NETTOHUUR)),

          if(VHE_EPA='F',(SUM(HP_MAXHUUR)*$(Streefnorm_F)-SUM(HP_NETTOHUUR)),

          if(VHE_EPA='G',(SUM(HP_MAXHUUR)*$(Streefnorm_G)-SUM(HP_NETTOHUUR)), (SUM(HP_MAXHUUR)*$(Streefnorm_overige) -SUM(HP_NETTOHUUR))

          ))))))) AS [Meerwaarde]   

           FROM

       

           FROM

      [C:\Users\----\Documents\Qlikview\Datasets\Huurprijzen.xlsx]

      (ooxml, embedded labels, table is [Rapport 1]);

        • Re: If statement in load script
          anbu cheliyan

          What is the value of the value variable Streefnorm_A?

          Do you have the field VHE_EPA in second table? Or using the field from table VGKenmerken?

            • Re: If statement in load script
              Ruud Cruchten

              Streefnorm A-G is a variable that the user can set between 0-1. VHE_EPA is in a different table than HP_MAXHUUR/NETTOHUUR. That's why I joined the table with MAXHUUR and NETTOHUUR with VGKENMERKEN (the table with VHE_EPA)

                • Re: If statement in load script
                  anbu cheliyan

                  //Vastgoed kenmerken

                  VGKenmerken:

                  LOAD VHE_ID,

                       VHE_EPA AS [Energielabel],

                       IF (CV='J', 'CV', 'HR') AS [Verwarmingseenheid],

                       IF(Coll='J', 'JA','Nee') AS [Collectief],

                       IF(Meter='J', 'Ja', 'Nee') AS [Meter]

                        

                  FROM

                  [C:\Users\----\Documents\Qlikview\Datasets\Vastgoed kenmerk.xlsx]

                  (ooxml, embedded labels, table is [Actueel bezit]);

                   

                  //Huurprijzen

                  Join (VGKenmerken)

                  LOAD VHE_ID,

                       Jaar,

                       HP_NETTOHUUR AS [Nettohuur_historisch],

                       HP_MAXHUUR AS [Maximumhuur_historisch]

                       FROM

                  [C:\Users\----\Documents\Qlikview\Datasets\Huurprijzen.xlsx]

                  (ooxml, embedded labels, table is [Rapport 1]);

                   

                   

                  Load VHE_ID,Jaar,HP_NETTOHUUR,HP_MAXHUUR,

                  Sum(if(VHE_EPA='A',((HP_MAXHUUR)*$(Streefnorm_A)-(HP_NETTOHUUR)),

                      if(VHE_EPA='B',((HP_MAXHUUR)*$(Streefnorm_B)-(HP_NETTOHUUR)),

                      if(VHE_EPA='C',((HP_MAXHUUR)*$(Streefnorm_C)-(HP_NETTOHUUR)),

                      if(VHE_EPA='D',((HP_MAXHUUR)*$(Streefnorm_D)-(HP_NETTOHUUR)),

                      if(VHE_EPA='E',((HP_MAXHUUR)*$(Streefnorm_E)-(HP_NETTOHUUR)),

                      if(VHE_EPA='F',((HP_MAXHUUR)*$(Streefnorm_F)-(HP_NETTOHUUR)),

                      if(VHE_EPA='G',((HP_MAXHUUR)*$(Streefnorm_G)-(HP_NETTOHUUR)), ((HP_MAXHUUR)*$(Streefnorm_overige) -(HP_NETTOHUUR))

                      )))))))) AS [Meerwaarde] Resident VGKenmerken Group by VHE_ID,Jaar,HP_NETTOHUUR,HP_MAXHUUR;

                    • Re: If statement in load script
                      Ruud Cruchten

                      If I add that to the script it gives the error doesn't recognise table

                        • Re: If statement in load script
                          Ruud Cruchten

                          Saw the problem, had the wrong tablename in the Resident function (changed it and forgot to change it there) but now I keep getting the error: Field HP_NETTOHUUR not found

                            • Re: If statement in load script
                              anbu cheliyan

                              Use these alias names in Group by clause

                                   HP_NETTOHUUR AS [Nettohuur_historisch],

                                   HP_MAXHUUR AS [Maximumhuur_historisch]

                                • Re: If statement in load script
                                  Ruud Cruchten

                                  That's wrong syntax, doesn't work

                                    • Re: If statement in load script
                                      anbu cheliyan

                                      Load VHE_ID,Jaar,

                                      [Nettohuur_historisch],[Maximumhuur_historisch]

                                       

                                      Sum(if(VHE_EPA='A',((HP_MAXHUUR)*$(Streefnorm_A)-(HP_NETTOHUUR)),

                                          if(VHE_EPA='B',((HP_MAXHUUR)*$(Streefnorm_B)-(HP_NETTOHUUR)),

                                          if(VHE_EPA='C',((HP_MAXHUUR)*$(Streefnorm_C)-(HP_NETTOHUUR)),

                                          if(VHE_EPA='D',((HP_MAXHUUR)*$(Streefnorm_D)-(HP_NETTOHUUR)),

                                          if(VHE_EPA='E',((HP_MAXHUUR)*$(Streefnorm_E)-(HP_NETTOHUUR)),

                                          if(VHE_EPA='F',((HP_MAXHUUR)*$(Streefnorm_F)-(HP_NETTOHUUR)),

                                          if(VHE_EPA='G',((HP_MAXHUUR)*$(Streefnorm_G)-(HP_NETTOHUUR)), ((HP_MAXHUUR)*$(Streefnorm_overige) -(HP_NETTOHUUR))

                                          )))))))) AS [Meerwaarde] Resident VGKenmerken Group by VHE_ID,Jaar,[Nettohuur_historisch],[Maximumhuur_historisch];

                                        • Re: If statement in load script
                                          Ruud Cruchten

                                          Yeah figured out that the problem was the names, got the same script as you provided. Got the error:

                                          Error in expression:

                                          ')' expected

                                           

                                          again....

                                            • Re: If statement in load script
                                              Ruud Cruchten

                                              Also tried this:

                                               

                                              Load VHE_ID,Jaar,[Nettohuur_historisch],[Maximumhuur_historisch],

                                               

                                              Sum(if([Energielabel]='A',([Maximumhuur_historisch]*$(Streefnorm_A)-[Nettohuur_historisch]),

                                               

                                                  if([Energielabel]='B',([Maximumhuur_historisch]*$(Streefnorm_B)-[Nettohuur_historisch]),

                                               

                                                  if([Energielabel]='C',([Maximumhuur_historisch]*$(Streefnorm_C)-[Nettohuur_historisch]),

                                               

                                                  if([Energielabel]='D',([Maximumhuur_historisch]*$(Streefnorm_D)-[Nettohuur_historisch]),

                                               

                                                  if([Energielabel]='E',([Maximumhuur_historisch]*$(Streefnorm_E)-[Nettohuur_historisch]),

                                               

                                                  if([Energielabel]='F',([Maximumhuur_historisch]*$(Streefnorm_F)-[Nettohuur_historisch]),

                                               

                                                  if([Energielabel]='G',([Maximumhuur_historisch]*$(Streefnorm_G)-[Nettohuur_historisch]), ([Maximumhuur_historisch]*$(Streefnorm_overige) -[Nettohuur_historisch])

                                               

                                                  )))))))) AS [Meerwaarde] Resident VGKenmerken Group by VHE_ID,Jaar,[Nettohuur_historisch],[Maximumhuur_historisch];

                            • Re: If statement in load script
                              jagan mohan rao appala

                              Hi,

                               

                              Your script seems to be ok.

                              What is the values of the variables Streefnorm_A & Streefnorm_overige?  Replace those variables with some values like 1 or 2 and try to reload, if you are able to reload then the problem is with your variables. 

                               

                              Also make sure that all the columns are there in the excel file.  If nothing works then attach some sample of the two tables.

                               

                              Hope this helps you.

                               

                              Regards,

                              jagan.