2 Replies Latest reply: Jul 28, 2016 3:01 AM by Christian Wolf

# Creating a logic with multiple joins

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.

Thanking you all in anticipation

• ###### Re: Creating a logic with multiple joins

Hi Karunpreet,

1. Use Concat for C2:

Left Join(TAB1)

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:

*,

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

Resident TAB1;

DROP Table TAB1;

RENAME Table TAB2 to TAB1;

next

See example.

regards

Christian

• ###### Re: Creating a logic with multiple joins

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: