Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
s_kunte23
Contributor III

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
Employee
Employee

Re: join/ removing specific combinations

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

5 Replies
s_kunte23
Contributor III

Re: join/ removing specific combinations

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

whiteline
Honored Contributor II

Re: join/ removing specific combinations

You can use WHERE statement with appropriate condition.

Employee
Employee

Re: join/ removing specific combinations

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

MVP
MVP

Re: join/ removing specific combinations

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
s_kunte23
Contributor III

Re: join/ removing specific combinations

works fine, thank you very much!

Community Browser