topic Re: left join with extra rows in QlikView

left join with extra rows

thabo2g5 — Fri, 21 Jul 2017 09:09:15 GMT

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

rahulpawarb — Fri, 21 Jul 2017 10:03:48 GMT

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

jmvilaplanap — Fri, 21 Jul 2017 10:29:06 GMT

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

thabo2g5 — Fri, 21 Jul 2017 12:15:54 GMT

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

jmvilaplanap — Fri, 21 Jul 2017 13:13:33 GMT

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

Re: left join with extra rows

mangalsk — Mon, 24 Jul 2017 07:30:31 GMT

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

Re: left join with extra rows

Anonymous — Mon, 24 Jul 2017 16:58:12 GMT

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.