topic Re: If statement in load script in QlikView

If statement in load script

Sun, 12 Oct 2014 21:23:25 GMT

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

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

Re: If statement in load script

anbu1984 — Mon, 13 Oct 2014 00:58:14 GMT

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

jagan — Mon, 13 Oct 2014 02:37:27 GMT

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.

Re: If statement in load script

Mon, 13 Oct 2014 07:08:55 GMT

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

Mon, 13 Oct 2014 07:38:23 GMT

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)

Re: If statement in load script

anbu1984 — Mon, 13 Oct 2014 08:21:56 GMT

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

Re: If statement in load script

Mon, 13 Oct 2014 08:51:38 GMT

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

Re: If statement in load script

Mon, 13 Oct 2014 09:13:39 GMT

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

anbu1984 — Mon, 13 Oct 2014 09:22:42 GMT

Use these alias names in Group by clause

HP_NETTOHUUR AS [Nettohuur_historisch],

HP_MAXHUUR AS [Maximumhuur_historisch]

Re: If statement in load script

Mon, 13 Oct 2014 09:39:00 GMT

That's wrong syntax, doesn't work

Re: If statement in load script

anbu1984 — Mon, 13 Oct 2014 09:41:51 GMT

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

Mon, 13 Oct 2014 09:50:42 GMT

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

Mon, 13 Oct 2014 09:53:35 GMT

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

anbu1984 — Mon, 13 Oct 2014 11:32:32 GMT

Can you attach sample input files.