topic IF statement - field not found in QlikView

IF statement - field not found

Mon, 02 May 2011 08:05:52 GMT

Hi,

I have following issue. I want to adjust QV document for different version of source data. Usually in newest version there are new columns available in tables. What I want to do in QV is that the document will decide if this new field will be loaded or not.

I used IF statement like:
*****************************************************
SET vDWLSourceVer=6.0109;

Load
IF($(vDWLSourceVer)>=7.0201,ColumnDate,NULL()) as [ColumnDate];
SQL SELECT * FROM FactTable;

The QV throw error: Field not found - <ColumnDate>, even it's not evaluated by IF clause? Is it there some other solution?

Any suggestion?

IF statement - field not found

Anonymous — Mon, 02 May 2011 08:09:31 GMT

Do you get the same error when you use:

Load ColumnDate;
SQL SELECT * FROM FactTable;

IF statement - field not found

Mon, 02 May 2011 08:13:25 GMT

Yes, I get, because I know that the ColumnDate isn't in the DB. The ColumnDate is in newer version of source DB (higher than 6.0109).

IF statement - field not found

erichshiino — Mon, 09 May 2011 02:11:43 GMT

If have to make an if-statement outside the load script.

Try something that looks like this:

SET vDWLSourceVer=6.0109;

IF($(vDWLSourceVer)>7.0201) THEN

Table:

Load <YOUR FIELDS WITHOUT ColumnDate> ,

null() as ColumnDate;

SQL SELECT * FROM FactTable;

ELSE

Table:

Load abc, def, ColumnDate;

SQL SELECT * FROM FactTable;

END IF

Hope it helps!

Erich Shiino

IF statement - field not found

Fri, 27 May 2011 05:14:43 GMT

Solved!

I applied variable text to be as column.

Like this:

SET vDWLSourceVer=6.0109;

SET vvColumnDate=if($(vDWLSourceVer)>=7.0201,'ColumnDate','NULL()');

Table:

Load

*,
$(vvColumnDate) as [ColumnDate];
SQL SELECT * FROM FactTable;

IF statement - field not found

Wed, 07 Mar 2012 02:06:11 GMT

I´m with a very similar trouble..

I have a folder with a lot of txt files.. two kinds of estructures..

one has 3 fields (A,B,C)
other has 2 fields (A,B)

How can I do a load script that if the file don´t have the C field load null() as C, if not, load C ?

I can´t do it munually because I don´t know who is who..

Look simple but i´m doing something wrong

IF statement - field not found

jagan — Wed, 07 Mar 2012 05:24:34 GMT

Hi,

Just use

TableName:

LOAD *

FROM DataSource;

Don't specify any column names, if the table has 2 columns it loads 2, if table has 3 it loads 3.

Regards,

Jagan.

IF statement - field not found

jagan — Wed, 07 Mar 2012 05:26:59 GMT

Hi,

You should the script as Shiino suggested, because it creates the column ColumnDate if it really has, but your script always creates a ColumnDate with Null/Values.

Regards,

Jagan.

IF statement - field not found

Tue, 13 Mar 2012 01:51:13 GMT

Dear Jagan,

Fernando Tonial help me with this point with a very simple way.

Firts of all, load null() with all files..

After, a Concatenate LOAD *..

that´s it..

best regards.