left join with extra rows

[thabo2g5]

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

[rahulpawarb]

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

[jmvilaplanap]

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

[thabo2g5]

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 ;

//

[jmvilaplanap]

Hi,

This columns are not in the script, I can't found avail1 for example.

This columns appears when you add the columns to the table object in QV

Regards

[mangalsk]

write one more condition in where clause for exists(Corection_id), so it will filter only that much id's. Sometimes join with exists give answer than only join

[Anonymous]

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.