6 Replies Latest reply: Jul 24, 2017 12:58 PM by Parimal Patil RSS

    left join with extra rows

    thabo moeketsi

      Hi everyone ,

       

      i have a problem with left join it brings extra data that i dont need how can i eliminate that data? i've attached an example of what left join returns

        • Re: left join with extra rows
          Rahul Pawar

          Hello Thabo,

           

          Given screenshot doesn't give more details?

          Such as:

          - Which table is joined to which table?

          - What is data before join in individual tables?

          - What is the current data after join and what is the expected results?

           

          Could you please share the application with some sample data? This will help us to cater your requirement in better manner.

           

          Regards!

          Rahul Pawar

            • Re: left join with extra rows
              thabo moeketsi

              hi

               

              thank for the rapid response i have two tables that i joined the problem is i have extra rows that i dont need(the picture i've uploaded )

               

              table 1 :

              LOAD  Distinct

                    Number ,

                    left(num(Created),5) as start_date,

                    If( WeekDay(left(num(Created),5))<5, 1, 0) as _IsWorkingday,

                    If( WeekDay(left(num(Created),5))=5, 1, 0) as _IsSaturday,

                    If( WeekDay(left(num(Created),5))=6, 1, 0) as _IsSunday,

                    Full_Business_Impact,

                    CORRELATION_ID ,

                    Full_Business_Impact/60 as Downtime

              from dataservice;

               

              table 2:

              LOAD Distinct COUNTRY,

                   SERVICES,

                   APPLICATIONS,

                   [CORRELATION ID] as CORRELATION_ID,

                   //////////////////////////////////////////////////////////////////////////////////////////

                     Floor(Round((END_TIME_WEEKDAY - START_TIME_WEEKDAYS) * 24 * 60)) as Duration_min_weekdays,

                    Floor(Round((END_TIME_SAT - START_TIME_SAT) * 24 * 60)) as Duration_min_saturday,

                    Floor(Round((END_TIME_SUN - START_TIME_SUN) * 24 * 60)) as Duration_min_sunday,

                   //////////////////////////////////////////////////////////////////////////////////////////

                   START_TIME_WEEKDAYS,

                   END_TIME_WEEKDAY,

                   START_TIME_SAT,

                   END_TIME_SAT,

                   START_TIME_SUN,

                   END_TIME_SUN,

                   TARGET

               

              from excel ;

              snowData:

               

              LOAD 

                      Distinct start_date ,

                      CORRELATION_ID ,

                      sum( distinct Downtime),

                      Number

                 

                   

                 

                 

              Resident Snow_Data

              group by start_date,Number,CORRELATION_ID;

               

              right Join(snowData)

              LOAD Distinct

                   COUNTRY,

                    CORRELATION_ID,

                    APPLICATIONS,

                    SERVICES,

                    Floor(Round((END_TIME_WEEKDAY - START_TIME_WEEKDAYS) * 24 * 60)) as Duration_Min_Weekdays

                   

              resident availibility  where  not IsNull (Duration_min_weekdays) or not IsNull (Duration_min_saturday) or not IsNull (Duration_min_sunday) ;

               

              left Join(snowData)

              LOAD  COUNTRY,

                    CORRELATION_ID,

                    APPLICATIONS,

                    SERVICES,

                    TARGET,

                   Floor(Round((END_TIME_SAT - START_TIME_SAT) * 24 * 60)) as Duration_Min_Saturday

                   

                   

              resident availibility  where Duration_min_saturday or Duration_min_weekdays or Duration_min_sunday ;

               

              left Join(snowData)

              LOAD  COUNTRY,

                    CORRELATION_ID,

                    APPLICATIONS,

                    SERVICES,

                    TARGET,

                  Floor(Round((END_TIME_SUN - START_TIME_SUN) * 24 * 60)) as Duration_Min_Sunday

                   

                   

              resident availibility  where  Duration_min_saturday or Duration_min_weekdays or Duration_min_sunday ;

               

              //

            • Re: left join with extra rows
              Jose Miguel Vilaplana Pascual

              Hi

               

              I don't know wich data do you need, but maybe you can try with other types of join:

              • Right Join
              • Inner Join
              • Outer Join

               

              Check this: Understanding Join, Keep and Concatenate

              • Re: left join with extra rows
                Parimal Patil

                Other attributes related to CORRELATION_ID like COUNTRY, APPLICATIONS, SERVICES, TARGET, in 2nd table might be forming more than one combinations. Please check the granularity of 2nd table. Instead use Left Keep, at least you will able to analyse the data from 2nd table. Hope this helps.