2 Replies Latest reply: Dec 7, 2012 12:45 PM by Lennart Frimannslund RSS

    Dynamic field renaming with mapping table from list of loaded fields

      Hi,

       

      I'm trying to dynamically rename loaded fields --- specifically I'm trying to rename fields that start with PK_ (primary key) into starting with FK_ (which means foreign key, but QV requires the same name, so FK_ it is.)

       

      So, I loop through the fields and build a mapping table, which looks fine except that it doesn't work. Can anyone tell me why? :-)

       

      // Data
      tab1:
      LOAD * Inline [
      FK_xxx,Country
      1,USA
      2,Canada
      3,UK]
      ;

      tab2:
      LOAD * Inline [
      PK_xxx,Continent
      1,North America
      2,North America
      3,Europe
      ]
      ;

      // For all tables, get field names, put them in table called f_names, field called FName
      for ii=1 to NoOfTables()-1  // -1 here because otherwise f_names table itself is included
        for jj=1 to NoOfFields(TableName($(ii)) )
             f_names:
            
      LOAD FieldName($(jj),TableName($(ii))) as FName
            
      Autogenerate 1;
        
      next
      next


      // Make mapping table
      map_1:
      mapping LOAD trim(FName) as xxx, trim('FK_' & mid(FName,4,len(FName)-3)) as Map_into
      Resident f_names
      where wildmatch(FName,'PK_*','GK_*') <> 0;

      // Copy of mapping table for debugging purposes
      LOAD trim(FName) as xxx, trim('FK_' & mid(FName,4,len(FName)-3)) as Map_into
      Resident f_names
      where wildmatch(FName,'PK_*','GK_*') <> 0;

      drop table f_names;

      // Everything seems fine until here, this command seemingly gets ignored -- or is the mapping table wrong?
      RENAME Fields using map_1;