topic Re: If statement with in condition in Archived Groups

If statement with in condition

Wed, 23 Jul 2025 14:48:52 GMT

I am trying to load following data sets but getting error. Could someone help debug?

Script:

LOAD security_id,
name,
instrument_type
FROM

(qvd);

CONCATENATE LOAD security_id,
begin_date,
end_date,
industry_level1,
industry_level1_desc,
If(instrument_type in ('CRNY','CD','INVTFRE','INVTFRET','INVTXRE','INVTXRET','STIF','TECP','VRDN'),'CASH', industry_level1) as industry_L1
FROM

(qvd)

Error Message

Re: If statement with in condition

swuehl — Fri, 22 Jan 2016 22:00:27 GMT

Try

...

If(Match(instrument_type ,'CRNY','CD','INVTFRE','INVTFRET','INVTXRE','INVTXRET','STIF','TECP','VRDN'),'CASH', industry_level1) as industry_L1

Re: If statement with in condition

Fri, 22 Jan 2016 22:06:09 GMT

Thanks Swuehl

It helped get past that error but not it is not recognizing instrument_type. I guess this may be because of using concatenation and since instrument_type is in the 1st table but not the 2nd where if statement is written. Any idea as to how to get around this?

Following is the error message.

Re: If statement with in condition

swuehl — Fri, 22 Jan 2016 22:14:02 GMT

Maybe you want to join your tables? Or use a MAPPING approach?

Not sure what you want to achieve, but you should be able to create a MAPPING table from table 1 to map instrument_type on security_id in table 2.

Re: If statement with in condition

Mon, 25 Jan 2016 14:11:36 GMT

Thanks Swuehl

Basically I am trying to achieve two things;

1. Join two tables and thereby combine all attributes of the two separate tables into one table. Concatenate is working fine for this.

2. Based on one of the attributes of 1st table i.e. if instrument_type = 'CRNY, VRDN, etc... flag industry_leve1 (which is one of the attributes in 2nd table) as cash and let all the other industry_level1 corresponding to all the other instrument types as it is.

Re: If statement with in condition

swuehl — Mon, 25 Jan 2016 14:23:41 GMT

Regarding your answer 1.:

JOIN and CONCATENATE is something different:

Understanding Join and Concatenate | Qlikview Cookbook

If you need to access attributes from the two tables within the scope of a record, you probably need to JOIN the tables (or MAP values), not concatenate the tables.

Re: If statement with in condition

Mon, 25 Jan 2016 19:01:43 GMT

Hi Swuel

Thanks for the revert. I now understand the difference between Concatenate and Outer join, and I am using outer join to join the two tables.

However, it is still not recognizing instrument_type_mod when I try to apply the logic to generate a "CASH" flag. Also read about Map but it doesn't look like applicable to this specific case.

Following is the syntax & error log.

sec_master:
LOAD security_id,
instrument_type,
If(Match(instrument_type,'CRNY','CD','INVTFRE','INVTFRET','INVTXRE','INVTXRET','STIF','TECP','VRDN'),'CASH', instrument_type) as instrument_type_mod
FROM
(qvd);

OUTER join LOAD
security_id,
classification,
begin_date,
end_date,
industry_level1,
industry_level1_desc
If(Match(instrument_type_mod,'CASH'),'CASH', industry_level1) as industry_L1
FROM
(qvd)
where classification = 'MSCI_SP_GICS'
and end_date = '3999-12-31';

Error Message:

Re: If statement with in condition

swuehl — Mon, 25 Jan 2016 19:12:00 GMT

You need to first to JOIN the two tables (without industry_L1 creation), then you can start to use instrument_type_mod and industry_level fields on the same record:

NOCONCATENATE LOAD *,

If(Match(instrument_type_mod,'CASH'),'CASH', industry_level1) as industry_L1

RESIDENT sec_master;

DROP TABLE sec_master;

Re: If statement with in condition

Mon, 25 Jan 2016 19:26:47 GMT

Thanks Swuehl. Highly appreciate your help.

Have a great day..!