topic Re: Concatenate/Join tables from 3 sources without duplication in QlikView

Concatenate/Join tables from 3 sources without duplication

Fri, 30 Jan 2015 16:16:44 GMT

I have three sources of data (source1, source2, source3) and I would like for them all to be joined into one table. I've been able to get close to the desired result using Concatenate or Outer Join, but not quite what I require as some records are being duplicated. To give an small example of my problem, my load script is as follows:

Table:

LOAD

Team

Division

Source

FROM source1

Concatenate

LOAD

Team

Division

Source

FROM source2

Concatenate

LOAD

Team

Division

Source

FROM source3

My problem arises when I have a Team which has changed Division in a different source. Each Team/Division combination should only show up once, but they're actually showing up three times. I only want the rows shaded green in my data:

Team	Division	Source
North London Guns	First	source1
North London Guns	First	source2
North London Guns	First	source3
North London Guns	Second	source1
North London Guns	Second	source2
North London Guns	Second	source3
North London Guns	Third	source1
North London Guns	Third	source2
North London Guns	Third	source3

All the other teams which don't have a different division between sources appear perfectly fine when concatenating or outer joining the tables (1 team per source).

Does anyone know how I can modify the script so that it doesn't produce the undesired rows above? Would some form of applymap be helpful?

Thanks

Re: Concatenate/Join tables from 3 sources without duplication

Anonymous — Fri, 30 Jan 2015 16:25:04 GMT

Try this condition on your last 2 load statements :

Where Not Exists(Team) And Not Exists(Division)

Also is the [Source] field actually required ?

Re: Concatenate/Join tables from 3 sources without duplication

marcus_sommer — Fri, 30 Jan 2015 16:29:09 GMT

Concatenate should be the best approach - if you then have duplicates put recno() and rowno() in your loads and/or check your source-files. Maybe they needed to be cleaned or you need another logic to identify them.

- Marcus

Re: Concatenate/Join tables from 3 sources without duplication

Mon, 02 Feb 2015 10:06:40 GMT

Thanks - I tried your solution, but it excluded too many values, so only the first green row in the table (source1) was loaded, but I also need the source2 and source3 rows. The Source field is required because I need to compare rows from the three sources (based on the source's date), or more specifically, I need to track any team that's changed division between the different sources (each source is a different season).

Re: Concatenate/Join tables from 3 sources without duplication

Mon, 02 Feb 2015 12:21:21 GMT

Thanks for the reply Marcus. I've placed recNo() and rowNo() in the three load statements but that hasn't changed anything as every row is already unique, but the problem is the rows in the table above that aren't shaded green don't actually exist in the raw data; they're being created at some point in the load, probably due to the concatenation. I'm unable to find a way of loading them all so that those extra rows aren't created, or so that too many rows are excluded from the load.

Re: Concatenate/Join tables from 3 sources without duplication

marcus_sommer — Mon, 02 Feb 2015 12:36:35 GMT

Try something like this:

Table:

LOAD Team, Division, Source, Team & '|' & Division as [UniqueKey] FROM source1;

Concatenate (Table)

LOAD Team, Division, Source FROM source2 where not exists([UniqueKey], Team & '|' & Division);

Concatenate (Table)

LOAD Team, Division, Source FROM source3 where not exists([UniqueKey], Team & '|' & Division);

- Marcus

Re: Concatenate/Join tables from 3 sources without duplication

Tue, 03 Feb 2015 11:30:00 GMT

Thanks Marcus - While the suggestion above didn't yield the results I require (it only showed source2 and source3 data that didn't exist in source1; in this case, only the first row), it did inspire my eventual solution/workaround.

I created a field (call it TeamDivision) which is a concatenation of Team & Division, separated by a "|":

Team & '|' & Division AS TeamDivision

Using this (for some reason I simply didn't notice this before I included the "|") I could see that the division matched the source on each row:

TeamDivision	Team	Division	Source
North London Guns\|First	North London Guns	First	source1
North London Guns\|Second	North London Guns	First	source2
North London Guns\|Third	North London Guns	First	source3
North London Guns\|First	North London Guns	Second	source1
North London Guns\|Second	North London Guns	Second	source2
North London Guns\|Third	North London Guns	Second	source3
North London Guns\|First	North London Guns	Third	source1
North London Guns\|Second	North London Guns	Third	source2
North London Guns\|Third	North London Guns	Third	source3

I then created another field which took a substring of the TeamDivision, based on that magical "|" which displays everything to the right of the "|"

mid([TeamDivision], index([TeamDivision],'|')+1) as TeamDivisionSub

And added it into the table:

TeamDivision	TeamDivisionSub	Team	Division	Source
North London Guns\|First	First	North London Guns	First	source1
North London Guns\|Second	Second	North London Guns	First	source2
North London Guns\|Third	Third	North London Guns	First	source3
North London Guns\|First	First	North London Guns	Second	source1
North London Guns\|Second	Second	North London Guns	Second	source2
North London Guns\|Third	Third	North London Guns	Second	source3
North London Guns\|First	First	North London Guns	Third	source1
North London Guns\|Second	Second	North London Guns	Third	source2
North London Guns\|Third	Third	North London Guns	Third	source3

Finally, I added an expression to the table which only shows the rows where TeamDivisionSub = Division, resulting in only the green rows 🙂

TeamDivision	TeamDivisionSub	Team	Division	Source
North London Guns\|First	First	North London Guns	First	source1
North London Guns\|Second	Second	North London Guns	Second	source2
North London Guns\|Third	Third	North London Guns	Third	source3

Team	Division	Source
North London Guns	First	source1
North London Guns	Second	source2
North London Guns	Third	source3

Thanks for your assistance!