Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

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

1 Solution

Accepted Solutions
Creator III
Creator III

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

View solution in original post

2 Replies
Creator III
Creator III

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

View solution in original post

Creator III
Creator III

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