Creating a logic with multiple joins

[Anonymous]

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

[cwolf · Accepted solution]

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;

next

See example.

regards

Christian

[cwolf]

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

next

TAB2:

LOAD

*,

$(vScript)

Resident TAB1;

regards

Christian