Load and overwrite contents of an attribute

[#1]

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?

[Anonymous · Accepted solution]

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]

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;

[#4]

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

[Anonymous]

No worries, glad you are sorted.