5 Replies Latest reply: Feb 20, 2014 9:11 AM by Fabrice Aunez RSS

    Creating Candidate key

      Hey Guise

      I have two different tables which use two different month Id for the same month now I wants to merge them but it is displaying only one's record and generate synthetic key now I wants to create a composite key for both fields and I have used syntax but  it got failed. So is there any body who helps me to create composite key or any other solution for this, that make the both table's record visible and identify the both fields  in my fact table with single field.

       

      Regards

      Surbhi Sahu

      Bisp Solutions Inc

      www.bisptrainings.com

        • Re: Creating Candidate key

          Hi John,

           

          If you are trying to concatenate the fields, then make a type cast to varchar and concatenate. It might help you out.

          • Re: Creating Candidate key
            Angad Singh

            What do you mean by two different month id for same month. Can you please attach a sample app.

             

            Thanks,

            Angad

            • Re: Creating Candidate key
              Mahesh Agrawal

              Hi Joha,

              U can form candidate key using ApplyMap().

              Pls see the following code and try it.

               

              Tab1:

              LOAD * INLINE [

                  ID, Name,address

                  a1, Mahesh,odisha

                  a2, Biren,haryana

                  a3, Gourav,andhra

                  a4, Achisman,andaman

              ];

               

               

              Tab2:

              LOAD * INLINE [

                  ID, Name

                  b1, Mahesh

                  b2, Biren

                  b3, Gourav

                  b4, Achisman

              ];

              //Mapping Table

              NameMap:

              Mapping LOAD Name,

                           ID

                     Resident

                           Tab2; 

                          

              Tab:

               

               

              LOAD ID&'-'&ApplyMap('NameMap',Name,'Not Found') as Candidate_Key ,

                    *,

                   ApplyMap('NameMap',Name,'Not Found') as ID1

                   Resident

                   Tab1;

                  

                  

              Drop Table Tab1,Tab2;   

               

               

              thnx

              • Re: Creating Candidate key
                Srikanth P

                Please post your script ?

                • Re: Creating Candidate key

                  If the months have the same names across the tables, you can use Autonumber() function. This function will return a key according to the parameter: and it will return always the same key if you pass the same parameter during the load.

                   

                  Fabrice