Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

If statement in load script

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

(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

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

13 Replies
anbu1984
Master III
Master III

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?

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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)

Not applicable
Author

I don't think the problem is in the variables since I copied this calculation from an expression. I want to load the expression as a field so to say. The expression works fine, only thing I've changed are the field names (e.g. HP_MAXHUUR instead of maximumhuur)

anbu1984
Master III
Master III

//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

(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

(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;

Not applicable
Author

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

Not applicable
Author

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

anbu1984
Master III
Master III

Use these alias names in Group by clause

     HP_NETTOHUUR AS [Nettohuur_historisch],

     HP_MAXHUUR AS [Maximumhuur_historisch]

Not applicable
Author

That's wrong syntax, doesn't work