join/ removing specific combinations

[Anonymous]

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

[Anonymous]

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

[whiteline]

You can use WHERE statement with appropriate condition.

[hic · Accepted solution]

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);

[jonathandienst]

Hi

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

[Anonymous]

works fine, thank you very much!