Create a new field with IF() function

[#1]

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!

[johnw]

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!

[#3]

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;

[#4]

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.