Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

thabo2g5
New 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
Valued Contributor II

Re: left join with extra rows

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
Valued Contributor

Re: left join with extra rows

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

Re: left join with extra rows

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
Valued Contributor

Re: left join with extra rows

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

Re: left join with extra rows

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

parimalpbi
New Contributor

Re: left join with extra rows

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.

Community Browser