topic Re: join/ removing specific combinations in QlikView

join/ removing specific combinations

Anonymous — Thu, 06 Sep 2012 11:45:04 GMT

Dear users,

I joined two completely different tables which results in a new table with every possible combination, like this:

column a	column b
a	a
a	b
a	c
a	d
a	e
b	a
b	b
b	c
b	d
b	e

...

The problem is that I have 2 times the combination a (col a) and b (col b) and vice versa b (col a) and a (col b). For data export purposes I need only one of these combinations, is there any possibility to cut the "doublets"?

Best

Stefan

Re: join/ removing specific combinations

Anonymous — Mon, 10 Sep 2012 07:35:18 GMT

No idea for a solution? It is urgent...

Re: join/ removing specific combinations

whiteline — Mon, 10 Sep 2012 08:04:03 GMT

You can use WHERE statement with appropriate condition.

Re: join/ removing specific combinations

hic — Mon, 10 Sep 2012 08:08:17 GMT

The following where clause will do the trick:

Load *,

ColA & '|' & ColB as Both

inline

[ColA, ColB

a,a

a,b

a,c

a,d

a,e

b,a

b,b

b,c

b,d

b,e]

Where not Exists(Both, ColB & '|' & ColA);

Re: join/ removing specific combinations

jonathandienst — Mon, 10 Sep 2012 08:10:55 GMT

If you join 2 unrelated tables, you will get a cross (or cartesian) join (every record in first table linked to each record in the second). What defines a "doublet" in your case?

Regards

Jonathan

Re: join/ removing specific combinations

Anonymous — Mon, 10 Sep 2012 10:55:50 GMT

works fine, thank you very much!