topic Re: Join Records by coincidence in different fields in QlikView

Join Records by coincidence in different fields

Tue, 06 Oct 2015 13:34:33 GMT

Guys,

I need to make a group of records. The condition to belong to this group is that they have to have the same value in at least one of three different fields. To give an example:

Cust	f1	f2	f3
a	1	-	300
b	1	-	100
c	-	10	-
d	2	10	-
e	3	10	100
f	4	30	500

In this Case the Customers, a,b,c,d and e, have to be group together, cause f1 joins a with b, f2 joins c,d and e, and f3 joins b with e.

and Customer f hasn't any connection in any of the three fields.

Can anyone help me on this? I'm really stuck on this.

Thanks,

Re: Join Records by coincidence in different fields

cwolf — Tue, 06 Oct 2015 20:27:48 GMT

I hope that helps you.

Re: Join Records by coincidence in different fields

Tue, 06 Oct 2015 20:38:07 GMT

Thanks Christian,

I'll try to understand the script structure and the functions you use (I've never used some of them), maybe I will ask you something more about this issue later.

Re: Join Records by coincidence in different fields

sunny_talwar — Tue, 06 Oct 2015 22:03:25 GMT

Are you looking for a output like this?

If yes, then try this:

Table:

LOAD Cust,

f1,

f2,

FROM

[https://community.qlik.com/thread/185645]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

LOAD Concat(Cust, ',') as Group,

Resident Table

Group By f1;

Concatenate(Temp)

LOAD Concat(Cust, ',') as Group,

Resident Table

Group By f2;

Concatenate(Temp)

LOAD Concat(Cust, ',') as Group,

Resident Table

Group By f3;

Final:

LOAD Concat(DISTINCT Group, ',') as Group;

LOAD SubField(Group, ',') as Group

Resident Temp

Where SubStringCount(Group, ',') > 0;

DROP Table Temp;

Re: Join Records by coincidence in different fields

Wed, 07 Oct 2015 14:17:33 GMT

Thanks Sunny,

Actually what i'm looking for is an output like the following table, I add the id column to show how the groups have to be.

Thank you again!!