Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Honored Contributor III

Re: If statement in load script

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?

MVP
MVP

Re: If statement in load script

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

Re: If statement in load script

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

Re: If statement in load script

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
Honored Contributor III

Re: If statement in load script

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

Re: If statement in load script

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

Not applicable

Re: If statement in load script

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
Honored Contributor III

Re: If statement in load script

Use these alias names in Group by clause

     HP_NETTOHUUR AS [Nettohuur_historisch],

     HP_MAXHUUR AS [Maximumhuur_historisch]

Not applicable

Re: If statement in load script

That's wrong syntax, doesn't work

Community Browser