10 Replies Latest reply: Nov 26, 2014 1:52 AM by sujeet singh RSS

    resident with two tables

    Amit Sharma

      Hi,

       

      I am using load script with resident. I need to use two tables as columns are from two tables.

      HOw to do it.

        • Re: resident with two tables
          chinna katiki

          Hey,

           

          If the two tables are having same columns you can simple load using resident load one after another using concatenate. So that it will work as union in oracle/sql.

           

          If the two tables are having 2 different columns and if you can just to pull the columns from two tables based on a possible join.

           

          If the two tables not have any possible common columns you cannot merge them.

           

          Hope this helps.

           

          BR,

          Chinna

            • Re: resident with two tables
              Marwen Garwachi

              Can you publish a sample file ?

              • Re: resident with two tables
                Amit Sharma

                Hi,

                 

                Thanks for quick reply.

                 

                Please find the script that I am using.

                 

                ValidBaselinetable:

                Load

                UID, sum(if(Result=3,1,0))/14 AS [Valid Baseline Compliance (%)]

                Resident CheckGraph

                group by UID;

                 

                BIOGraph:

                Load UID,

                     'Active Schedules' as Category,

                     if( [Project Status / State] ='Active',1,0) as value

                Resident ProjectTracker;

                Concatenate (BIOGraph)

                Load UID,

                     '100% Valid Baseline Compliant' as Category,

                     if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

                Resident ProjectTracker;

                 

                Here [Valid Baseline] is part of projecttracker table but [Valid Baseline Compliance (%)] is part of ValidBaselinetable table, So while reloading it is giving error field not found.

                 

                Can you provide me the script for that.

                 

                 

                Thanks

                 

                  • Re: resident with two tables
                    Farai HURUBA

                    Hi Amit Sharma ,

                     

                    U see you [Valid Baseline Compliance (%)] field is not in Project tracker that is why you having that error

                    try to join the table where [Valid Baseline Compliance (%)] is in to your  ProjectTracker before the last resident load

                      • Re: resident with two tables
                        Amit Sharma

                        How to do this. Can you give me the query?

                        Thanks

                        • Re: resident with two tables
                          Farai HURUBA

                          BIOGraphTemp:

                          Load UID,

                               'Active Schedules' as Category,

                               if( [Project Status / State] ='Active',1,0) as value

                          ,[Valid Baseline]

                          Resident ProjectTracker ;

                           

                          left join(BIOGraph)  // use proper join according to data

                           

                          ValidBaselinetable:

                          Load

                          UID, sum(if(Result=3,1,0))/14 AS [Valid Baseline Compliance (%)]

                          Resident CheckGraph

                          group by UID;

                          BIOGraph:

                          load

                          Category,

                          if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

                          Resident BIOGraphTemp;

                          Concatenate (BIOGraph)

                          Load UID,

                                Category,

                                value

                          Resident ProjectTracker;

                        • Re: resident with two tables
                          chinna katiki

                          ValidBaselinetable:

                          Load

                          UID, sum(if(Result=3,1,0))/14 AS [Valid Baseline Compliance (%)]

                          Resident CheckGraph

                          group by UID;

                           

                          BIOGraph:

                          Load UID,

                               'Active Schedules' as Category,

                               if( [Project Status / State] ='Active',1,0) as value

                          Resident ProjectTracker;

                          join (BIOGraph)

                          Load UID,

                               '100% Valid Baseline Compliant' as Category,

                               if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

                          Resident ValidBaselinetable;

                           

                           

                          Try this..

                            • Re: resident with two tables
                              Amit Sharma

                              I tried, but giving error

                               

                              Field not found - <Valid Baseline>

                              join (BIOGraph)

                              Load UID,

                                   '100% Valid Baseline Compliant' as Category,

                                   if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

                              Resident ValidBaselinetable

                               

                              because valid baseline is part of projecttracker table.

                            • Re: Re: resident with two tables
                              chinna katiki

                              Hi,

                              here it is...

                               

                              ValidBaselinetable:

                              Load

                              UID, sum(if(Result=3,1,0))/14 AS [Valid Baseline Compliance (%)]

                              Resident CheckGraph

                              group by UID;

                               

                              BIOGraphtmp:

                              Noconcatenate

                              Load UID,

                                  'Active Schedules' as Category,

                                  if( [Project Status / State] ='Active',1,0) as value,

                              [Valid Baseline]

                              Resident ProjectTracker;

                              join//(ValidBaselinetable)

                              Load UID, [Valid Baseline Compliance (%)] resident ValidBaselinetable;

                               

                              BIOGraph:

                              Noconcatenate

                              Load UID,

                                Category,

                                  value

                              Resident BioGraphtmp where Category ='Active Schedules';

                              Concatenate (BIOGraph)

                              Load UID,

                                  '100% Valid Baseline Compliant' as Category,

                                  if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

                              Resident BioGraphtmp;

                               

                              Drop table BioGraphtmp;

                               

                              You may need to remove the Projecttracker and ValidBaseline tables inorder to avoid synthetic keys as in BIOGrpah tables also we are representing the UID column with same name.

                               

                              Hope this helps.

                              Best regards,

                              Chinna

                               

                              PFA Sample example for resident from two tables for your reference.

                          • Re: resident with two tables
                            sujeet singh

                            Amit first come in more words please.

                            Explain the table fields and key to link