7 Replies Latest reply: Jan 23, 2013 8:45 AM by Roland Kunle RSS

    Table Alias Name

    Nandha Kumar

      Hi All,

       

      I want to change the field below names and give alias name by not using "as" function in my script. Can any one help me here?

      Also pls explain me how it will work.

       

       

      GC_2:

       

      LOAD Year,
      [CONTRACT EFFECTIVE DATE],

          
      [CONTRACT EXPIRY DATE],

          
      CATEGORY,

          
      [Facility Of Benefit],

          
      STATUS ,

          
      [CLAIM TYPE] ,

          
      [SPEC ASSESSMENT],

          
      [TREATMENT DATE],

          
      [INVOICE NUMBER],

          
      [AUTHORIZATION NUMBER],

          
      PROVIDER,

          
      SERVICE,

          
      [ITEM CPT CODE],

          
      [QTY APPROVED],

          
      [CLAIMED AMOUNT],

          
      [APPROVED AMOUNT],

          
      [ITE MBENEFICIARY SHARE]

       

      FROM

      [..\Data\GC_-_SAMPLE_2.xlsx]

      (
      ooxml, embedded labels, table is GC1);

       

      Rgds,

      Nandha

        • Re: Table Alias Name
          Kumar Natarajan

          Hi,

           

           

          If you say

           

          Qualify *;

           

          This will qualify all the fields such that they are tablename.fieldname.

           

          Example

          Qualify *;

          Table1:

          LOAD

          A,

          B

          FROM DataSource;

            • Re: Table Alias Name
              Nandha Kumar

              Hi Kumar,

               

              Yes you are right its Qualify.

               

              I have given my script in this discussion. Can you pls explain me or step by step process for this Qualify concept?

               

               

              Rgds,

              Nandha

                • Re: Table Alias Name
                  khadeer basha

                  Add before your load:

                  Qualify *;

                  • Re: Table Alias Name
                    Saurabh Pandit

                    QUALIFY *;

                    GC_2:

                     

                    LOAD Year,
                    [CONTRACT EFFECTIVE DATE],

                        
                    [CONTRACT EXPIRY DATE],

                        
                    CATEGORY,

                        
                    [Facility Of Benefit],

                        
                    STATUS ,

                        
                    [CLAIM TYPE] ,

                        
                    [SPEC ASSESSMENT],

                        
                    [TREATMENT DATE],

                        
                    [INVOICE NUMBER],

                        
                    [AUTHORIZATION NUMBER],

                        
                    PROVIDER,

                        
                    SERVICE,

                        
                    [ITEM CPT CODE],

                        
                    [QTY APPROVED],

                        
                    [CLAIMED AMOUNT],

                        
                    [APPROVED AMOUNT],

                        
                    [ITE MBENEFICIARY SHARE]

                     

                    FROM

                    [..\Data\GC_-_SAMPLE_2.xlsx]

                    (
                    ooxml, embedded labels, table is GC1);

                     

                    Your resulting load will have the field names renames as Tablename.Fieldname

                    Eg. [GC_2.Approved Amount]

                     

                    Hope it helps

                • Re: Table Alias Name
                  Mohit Sharma

                  using qualify *;

                  you get the fieldname new(i.e tablename.fieldname)

                  before loading table write qualify *;

                  then after loading statement and reloading that you can get the fields with name of tablename.fieldname

                  like

                  a:

                  LOAD * INLINE [

                      data, value

                       qwr, 10

                        wee, 20

                         www, 30

                           g, 40

                           y, 50

                           tyui, 60

                  ];

                  spacemap:

                  Mapping LOAD

                  Repeat(' ',RecNo()),' '

                  AutoGenerate 10;

                  QUALIFY*;

                  LOAD 

                  data,

                  value,

                  MapSubString('spacemap',Trim(data))as y

                  Resident a ;

                  DROP Table a;

                  hope it helps you.

                  • Re: Table Alias Name
                    jagan mohan rao appala

                    Hi Nandha,

                     

                    Please find below example

                     

                    Qualify *;

                    X:

                    Load A,B from x.csv;

                     

                     

                    Now column A is renamed as X.A and B is renamed as X.B.

                     

                    Hope this helps you.

                     

                    Regards,

                    Jagan.

                    • Re: Table Alias Name

                      Hi Nandha,

                       

                      may be you can use the "rename fields" - statement with a prior defined mapping table. Similar to this:

                       

                      // create Table with aliases via mapping load:

                      FieldMap:

                      Mapping Load * inline [

                      OldName, NewName

                      Year, NewYear
                      CONTRACT EFFECTIVE DATE,  MyContractDate

                      ]; // one line per field to rename

                       

                       

                      // now the normal load :

                      LOAD Year,
                      [CONTRACT EFFECTIVE DATE],

                      . . . .

                       

                      // rename the fields listed in mapping table "FieldMap":

                      RENAME Fields using FieldMap;

                       

                       

                      HtH

                      Roland