4 Replies Latest reply: Jan 26, 2016 3:06 PM by Bill Markham RSS

    Load and overwrite contents of an attribute

    Hemang Dave

      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
      [B:\DataMart\RawData\TRPRef\PROD\v_security_master.qvd] (
      qvd);

       

      UTER join LOAD
      security_id,
      industry_level1_desc as MSCI_industry_level1_desc
      FROM
      [B:\DataMart\RawData\TRPRef\PROD\v_security_industry_classification_pvt.qvd] (
      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?