topic Creating a logic with multiple joins in QlikView

Creating a logic with multiple joins

Anonymous — Tue, 26 Jul 2016 06:19:44 GMT

Hello All,

I am facing a problem in creating a logic and would require your help for that.

I have a table which looks something like

C1	C2	C3
a	5	1
b	6	1
c	7	1
d	8	2
e	2	2
f	4	3
g	5	3
h	2	3
i	6	4
j	7	5
k		5
l	6	5

I want a result which should look like

C1	C2	C3	T1	T2	T3
a	5	1	5	6	7
b	6	1	5	6	7
c	7	1	5	6	7
d	8	2	8	2	0
e	2	2	8	2	0
f	4	3	4	5	2
g	5	3	4	5	2
h	2	3	4	5	2
i	6	4	6	0	0
j	7	5	7	0	6
k		5	7	0	6
l	6	5	7	0	6

As you can see, column C3 has 5 distinct values 1,2,3,4 and 5 . The count for max values for C3 is 3 hence 3 new columns are added in the output table (T1,T2 and T3).

If supposingly we had 4 as the max count then 4 new columns should be added.

The values in T1, T2 and T3 are populated on the basis of C2.

Please help guys its urgent.

Thanking you all in anticipation

Re: Creating a logic with multiple joins

cwolf — Wed, 27 Jul 2016 18:12:03 GMT

Hi Karunpreet,

1. Use Concat for C2:

Left Join(TAB1)

LOAD

C3,

Concat(C2,',',C2) as Temp

Resident TAB1

Group by C3;

2. Use Subfield to create the new Fields:

for i=1 to vMaxCnt

TAB2:

LOAD

SubField(Temp,',',$(i)) as 'T$(i)'

Resident TAB1;

DROP Table TAB1;

RENAME Table TAB2 to TAB1;

See example.

regards

Christian

Re: Creating a logic with multiple joins

cwolf — Thu, 28 Jul 2016 07:01:15 GMT

Hi Karunpreet,

a better solution for part 2 is to build a "Script line" in the loop:

for i=1 to vMaxCnt

if i=1 then

set vScript="subfield(Temp,',',$(i)) as T$(i)";

else

set vScript="$(vScript),subfield(Temp,',',$(i)) as T$(i)";

end if

TAB2:

LOAD

$(vScript)

Resident TAB1;

regards

Christian

C1	C2	C3	T1	T2	T3
a	5	1	5	6	7
b	6	1	5	6	7
c	7	1	5	6	7
d	8	2	8	2	0
e	2	2	8	2	0
f	4	3	4	5	2
g	5	3	4	5	2
h	2	3	4	5	2
i	6	4	6	0	0
j	7	5	7	0	6
k		5	7	0	6
l	6	5	7	0	6

C1	C2	C3	T1	T2	T3
a	5	1	5	6	7
b	6	1	5	6	7
c	7	1	5	6	7
d	8	2	8	2	0
e	2	2	8	2	0
f	4	3	4	5	2
g	5	3	4	5	2
h	2	3	4	5	2
i	6	4	6	0	0
j	7	5	7	0	6
k		5	7	0	6
l	6	5	7	0	6

C1	C2	C3	T1	T2	T3
a	5	1	5	6	7
b	6	1	5	6	7
c	7	1	5	6	7
d	8	2	8	2	0
e	2	2	8	2	0
f	4	3	4	5	2
g	5	3	4	5	2
h	2	3	4	5	2
i	6	4	6	0	0
j	7	5	7	0	6
k		5	7	0	6
l	6	5	7	0	6