Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thabo2g5
Contributor III
Contributor III

left join with extra rows

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

6 Replies
rahulpawarb
Specialist III
Specialist III

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
Specialist
Specialist

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
Contributor III
Contributor III
Author

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
Specialist
Specialist

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
Creator III
Creator III

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
Not applicable

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.