Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load and overwrite contents of an attribute

Hi

 

I have two tables:

 

  1. sec_master and
  2. security_classification

   

They are combined using outer join and using security_id (key). On the resulting table sec_master, I need to apply the following logic;

 

If instrument_type is CRNY, CD, or VRDN

then industry_lvel1_desc = CASH

else industry_lvel1_desc = industry_lvel1_desc

Essentially, I need to overwrite industry_lvl1_desc with Cash for records where instrument types is CRNY, CD or VRDN.

 

sec_master:
LOAD security_id,
instrument_type
FROM
(
qvd);

 

UTER join LOAD
security_id,
industry_level1_desc as MSCI_industry_level1_desc
FROM
(
qvd) ;

 

NOCONCATENATE LOAD *,
If(Match(instrument_type,'CRNY','CD','VRDN'), 'CASH', MSCI_industry_level1_desc) as MSCI_industry_level1_desc_L1

RESIDENT sec_master;
DROP TABLE sec_master;

 

The code is working if I create a separate attribute and call it for example MSCI_industry_level1_desc_L1.

However, the goal is to overwrite the attribute MSCI_industry_level1_desc which is not working.

For example, it doesn't work if I use the line

 

if(Match(instrument_type,'CRNY','CD','INVTFRE','INVTFRET','INVTXRE','INVTXRET','STIF','TECP','VRDN'), 'CASH', MSCI_industry_level1_desc)

 

INSTEAD OF THE LINE

 

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

 

How to overwrite the attribute MSCI_industry_level1_desc?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

For your NOCONCATENATE LOAD try replacing the * with the 2 fields you want to carry forward and not the one you don't want, maybe something like this :

NOCONCATENATE LOAD

     security_id,
     instrument_type,

     If(Match(instrument_type,'CRNY','CD','VRDN'), 'CASH', MSCI_industry_level1_desc) as MSCI_industry_level1_desc
RESIDENT sec_master;


Otherwise with your * you will be trying to create the calculated field with the same name as one you already have.



View solution in original post

4 Replies
Anonymous
Not applicable
Author

For your NOCONCATENATE LOAD try replacing the * with the 2 fields you want to carry forward and not the one you don't want, maybe something like this :

NOCONCATENATE LOAD

     security_id,
     instrument_type,

     If(Match(instrument_type,'CRNY','CD','VRDN'), 'CASH', MSCI_industry_level1_desc) as MSCI_industry_level1_desc
RESIDENT sec_master;


Otherwise with your * you will be trying to create the calculated field with the same name as one you already have.



maxgro
MVP
MVP

don't use the * but the field names in the resident load (last lload)

sec_master:
LOAD

     security_id,
     instrument_type
FROM
(
qvd);

OUTER join LOAD
     security_id,
     industry_level1_desc as MSCI_industry_level1_desc
FROM
(
qvd) ;


NOCONCATENATE

LOAD

     security_id,

     instrument_type,
     If(Match(instrument_type,'CRNY','CD','VRDN'), 'CASH', MSCI_industry_level1_desc) as MSCI_industry_level1_desc
RESIDENT sec_master;
DROP TABLE
sec_master;

Not applicable
Author

Thanks Bill. That was very useful. Appreciate your help.!

Anonymous
Not applicable
Author

No worries, glad you are sorted.