Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

karunpreet
Contributor

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

   

C1C2C3
a51
b61
c71
d82
e22
f43
g53
h23
i64
j75
k 5
l65

I want a result which should look like

   

C1C2C3T1T2T3
a51567
b61567
c71567
d82820
e22820
f43452
g53452
h23452
i64600
j75706
k 5706
l65706

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

Tags (1)
1 Solution

Accepted Solutions
chrwolf64
Contributor III

Re: Creating a logic with multiple joins

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

2 Replies
chrwolf64
Contributor III

Re: Creating a logic with multiple joins

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

chrwolf64
Contributor III

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:

LOAD

*,

$(vScript)

Resident TAB1;

regards

Christian

Community Browser