topic Create a new field with IF() function in QlikView

Create a new field with IF() function

Tue, 14 Jul 2009 05:45:12 GMT

Hi,

I have loaded some data from a SQL server (see sample script below):

cbr_1:

SQL SELECT "local_council",
"county_council",
"state_council"
FROM "VPS_MD".dbo."cbr_1";

The fields are character flags with two possible values: (Y)es or (N)o
Now, I try to create another field - "public_sector" - and fill it in the following way:

LOAD IF([local_council]='Y' OR [County_council]='Y' OR [state_council]='Y','Yes','No') AS 'public_sector'
RESIDENT cbr_2 ;

In other words - if at least one of the the original flags are set to "Y", public_sector should be set to "Yes", otherwise "No".

When I run the script, the new field "public_sector" is created but values are always null, never "Yes" or "No".

I guess my ambitions are far greater than my skills in Qlikview... I hope someone can help me!

Create a new field with IF() function

johnw — Tue, 14 Jul 2009 06:16:32 GMT

Well, your resident table is cbr_1, not cbr_2. And public_sector shouldn't be in single quotes since it is a field name. But I'm guessing those were just typos when you wrote the post, and not the actual cause of the problem.

Other than that, I'm not seeing it. It reads fine to me. Sorry!

Create a new field with IF() function

Tue, 14 Jul 2009 06:51:36 GMT

Table1:

LOAD * INLINE [
LC, CC, SC
'N', 'N', 'Y'
];

LOAD IF(LC='Y' OR CC='Y' OR SC='Y','Yes','No') AS public_sector
RESIDENT Table1;

Create a new field with IF() function

Tue, 14 Jul 2009 19:04:57 GMT

Thanks John and Sachin for your hints.

I got rid of the problem when I reloaded all SQL-tables (and all fields) with LOAD RESIDENT command and then dropped the original tables. That is:

cbr_0:

SQL SELECT * FROM (SQL database)

cbr_1:

LOAD *,IF(LC='Y' OR CC='Y' OR SC='Y','Yes','No') AS public_sector RESIDENT cbr_0

DROP TABLE cbr_0

Now I can manipulate all data with Qlikview functions without the limitations of the SQL-syntax.