Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

join/ removing specific combinations

Dear users,

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

column acolumn b
aa
ab
ac
ad
ae
ba
bb
bc
bd
be

...

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

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

5 Replies
Anonymous
Not applicable
Author

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

whiteline
Master II
Master II

You can use WHERE statement with appropriate condition.

hic
Former Employee
Former Employee

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

works fine, thank you very much!