Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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?

Tags (2)
1 Solution

Accepted Solutions
bill_markham
Not applicable

Re: Load and overwrite contents of an attribute

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.



4 Replies
bill_markham
Not applicable

Re: Load and overwrite contents of an attribute

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
Not applicable

Re: Load and overwrite contents of an attribute

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

Re: Load and overwrite contents of an attribute

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

bill_markham
Not applicable

Re: Load and overwrite contents of an attribute

No worries, glad you are sorted.