9 Replies Latest reply: Oct 8, 2013 10:13 AM by Jaswant Chowdary RSS

    Applymap ()

    Jaswant Chowdary


      Hi Friends,

       

      I am to Forum and seeking help of the below scenario.

      I would like to build table by using below 3 mapping tables.

       

      Inputs:

      =======

      Input table:

      a,

      b,

      c

      from Input table;

       

      Map_tab1:

      a,

      desc1

       

      Map_tab2:

      desc1,

      desc2

      Map_tab3

      desc2,

      desc3

       

      Expected O/P Table:

       

      Field1Description
      adesc1
      desc1desc2
      desc2desc3

       

      I used below script but sofar not succieded.

       

       

      [AppToDesc]:

      Load
      a,
      ApplyMap('Map_tab1',a,a as desc1,
      ApplyMap('Map_tab2',Peek('desc1',,'AppToDesc1'),desc1) as desc2,

      ApplyMap('Map_tab3',Peek('desc2',,'AppToDesc2'),desc2) as desc3,

      Resident [Input table];
      Here i am getting error message says that field 'desc1' not found.

       

       

       

       

      Thanks,

      JaswantC.

        • Re: Applymap ()
          jagan mohan rao appala

          Hi Jaswant,

           

          Can you attach some sample data/ file, it helps in understanding.  Seems that your script is wrong can you post entire script.

           

          Regards,

          Jagan.

            • Re: Re: Applymap ()
              Jaswant Chowdary

              Hi Jagan,

               

              Please find attached app and i am looking below result from the app.

               

              StateCodeCapitalCountyStareetNM
              MABostonFraminghamGraniteST
              CASacramentoQuincyQuincy
              TXDallasGreenVilleAdamsStreet
                • Re: Re: Re: Applymap ()
                  Jaswant Chowdary


                  Hi Jagan,

                   

                  Some how i am close to solution but i would like to get rid of duplicate records if i use distinct again i am not getting desired results.

                    • Re: Applymap ()
                      mayilvahanan ramasamy

                      Hi

                       

                      Try like this

                       

                      Input:

                      LOAD * INLINE [

                          StateCode

                          MA

                          CA

                          TX

                          NH

                      ];

                       

                      Map_tab1:

                      Mapping LOAD * INLINE [

                          StateCode, Capital

                           MA, Boston

                           CA, Sacramento

                           TX, Dallas

                      ];

                       

                       

                      Map_tab2:

                      Mapping LOAD * INLINE [

                          Capital, County

                          Boston, Framingham

                          Sacramento, Quincy

                          Dallas, Greenville

                         ];

                       

                      Map_tab3:

                      Mapping LOAD * INLINE [

                          County, StreetNM

                          Framingham, GraniteSt

                          Greenville, Admasstreet

                      ];

                       

                      FieldToStreet:

                      LOAD *, ApplyMap('Map_tab3',County,County) as [StreetNM];

                      LOAD *, ApplyMap('Map_tab2',Capital,Capital) as [County];

                      Load

                      StateCode as StateCode,

                      ApplyMap('Map_tab1',StateCode,StateCode) as [Capital]

                      Resident Input;

                      • Re: Applymap ()
                        Jonathan Dienst

                        Hi

                         

                        I am not sure of your requirements, but to use nested applymaps, then do something like:

                         

                        [AppToDesc]:

                        Load

                          a,

                          ApplyMap('Map_tab1', a) as desc1,

                          ApplyMap('Map_tab2', ApplyMap('Map_tab1', a)) as desc2,

                          ApplyMap('Map_tab3', ApplyMap('Map_tab2', ApplyMap('Map_tab1', a))) as desc3

                        Resident [Input table];

                         

                        Regards

                        Jonathan

                      • Re: Applymap ()
                        jagan mohan rao appala

                        Hi,

                         

                        Please find the script below

                         

                         


                        Input:

                        LOAD * INLINE [
                        StateCode
                        MA
                        CA
                        TX
                        NH
                        ]
                        ;



                        Map_tab1:
                        Mapping LOAD * INLINE [
                        StateCode, Capital
                        MA, Boston
                        CA, Sacramento
                        TX, Dallas
                        ]
                        ;

                        Map_tab2:
                        Mapping LOAD * INLINE [
                        Capital, County
                        Boston, Framingham
                        Sacramento, Quincy
                        Dallas, Greenville
                        ]
                        ;

                        Map_tab3:
                        Mapping LOAD * INLINE [
                        County, StreetNM
                        Framingham, GraniteSt
                        Greenville, Admasstreet
                        ]
                        ;

                        FieldToStreet:
                        LOAD
                        *,
                        ApplyMap('Map_tab2',Peek('County'),County) as [StreetNM];
                        LOAD
                        *,
                        ApplyMap('Map_tab2', Capital,'N/A') as [County];
                        Load
                        StateCode as StateCode,
                        ApplyMap('Map_tab1',StateCode,'N/A') as [Capital]
                        Resident Input;


                        DROP TABLE Input;

                         

                        Regadrs,

                        Jagan.

                         

                         

                         


                    • Re: Applymap ()
                      Gysbert Wassenaar

                      Try

                       

                      Map_tab1:
                      Mapping LOAD * INLINE [
                          StateCode, Capital
                           MA, Boston
                           CA, Sacramento
                           TX, Dallas
                      ];
                      
                      
                      Map_tab2:
                      Mapping LOAD * INLINE [
                          Capital, County
                          Boston, Framingham
                          Sacramento, Quincy
                          Dallas, Greenville
                         ];
                      
                      Map_tab3:
                      Mapping LOAD * INLINE [
                          County, StreetNM
                          Framingham, GraniteSt
                          Greenville, Admasstreet
                      ];
                      
                      Input:
                      LOAD
                          StateCode
                          , ApplyMap('Map_tab1',StateCode) as Capital
                          , ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode)) as County
                          , ApplyMap('Map_tab3',ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode))) as StreetNM   
                      INLINE [
                          StateCode
                          MA
                          CA
                          TX
                          NH
                      ];
                      
                      • Re: Applymap ()

                          Use this code and run. Tried with Jonathan approach. It works fine if we create with nested applymap.

                        Input:

                        LOAD * INLINE [
                        StateCode
                        MA
                        CA
                        TX
                        NH
                        ]
                        ;



                        Map_tab1:
                        Mapping LOAD * INLINE [
                        StateCode, Capital
                        MA, Boston
                        CA, Sacramento
                        TX, Dallas
                        ]
                        ;

                        Map_tab2:
                        Mapping LOAD * INLINE [
                        Capital, County
                        Boston, Framingham
                        Sacramento, Quincy
                        Dallas, Greenville
                        ]
                        ;

                        Map_tab3:
                        mapping LOAD * INLINE [
                        County, StreetNM
                        Framingham, GraniteSt
                        Greenville, Admasstreet
                        ]
                        ;

                        FieldToStreet:
                        Load
                        StateCode as StateCode,
                        ApplyMap('Map_tab1',StateCode) as [Capital],
                        ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode)) as [County],
                        ApplyMap('Map_tab3',ApplyMap('Map_tab2',ApplyMap('Map_tab1',StateCode))) as [StreetNM]
                        Resident Input;

                        DROP Table Input;