If statement with in condition

[#1]

Hi

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

[swuehl]

Try

...

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

[#3]

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.

[swuehl]

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.

[#5]

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.

[swuehl]

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.

[#7]

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:

[swuehl · Accepted solution]

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;

[#9]

Thanks Swuehl. Highly appreciate your help.

Have a great day..!