2 Replies Latest reply: Dec 10, 2014 9:19 PM by jagan mohan rao appala RSS

    join/mapping issue

    phillip colbert

      Hi,

       

      I have two datasets, 1 old and 1 new which I have joined via the PK.

       

      Both contain 2 columns, Sub_Code and  Sub_Name

       

      Problem is the Sub_Code/Sub_Names in the new dataset have been aggregated (there are more subjects in the old dataset, fewer in the new dataset) – and therefore I need to map the two so that when something is selected in one dataset the relevant is selected in the other dataset.

       

      Ie.

       

      In the old dataset:

      Sub_Code                                           Sub_Name                      

      1                                                         Soup                                   

      2                                                         Bread                                 

      3                                                         Cheese                              

      4                                                         Water

       

      In the new dataset:

      Sub_Code                                           Sub_Name

      1                                                         Food

      2                                                         Beverage

       

      So if Sub_Code 1 was selected in the new dataset, Sub_Code’s 1,2 & 3 would be displayed from the old dataset.

       

      Any ideas? I thought of creating a key or something via an inline load?

        • Re: join/mapping issue
          Srikanth P

          As I understand your question correctly, the new data set have different sub code names than old data set and more like old data set sub code category.

           

          Please create the a Mapping table either Inline or Excel , to add new sub code called Sub Code Category.

           

          Map_Category:

          LOAD * INLINE [

          OLD_SUB_CODE , NEW_SUB_CODE

          Soup ,  Food

          Chesee , Food

          Bread ,  Food

          Water , Beverage

          .....

          ] ;

           

          LOAD

               Sub_Code_ID ,

               Sub_Code_Name ,

               ApplyMap( 'Map_Category' , Sub_Code_Name  , null() ) AS Sub_Code_Category

          From Source.qvd (qvd) ;

          • Re: join/mapping issue
            jagan mohan rao appala

            Hi,

             

            There should be some mapping between the old and new data sets, like below

             

            In the old dataset:

            Sub_Code          Old_Sub_Code                                           Sub_Name                     

            1                                   1                                                         Soup                                  

            1                                   2                                                         Bread                                

            1                                   3                                                         Cheese                             

            2                                   4                                                         Water

             

            In the new dataset:

            Sub_Code                                           Sub_Name

            1                                                         Food

            2                                                         Beverage

             

            Do this in Excel file, if there is any changes then just updating the Excel is easier instead of changing in Inline script.

             

            Hope this helps you.

             

            Regards,

            Jagan.