topic Re: aggr and concat problem in QlikView

aggr and concat problem

kuschcu66 — Tue, 05 Dec 2023 16:12:08 GMT

I want to concanated each data in the 2nd table below by the data in the 1st table.

TABLE 1
key	table1_value_field
A	1
B	2
C	3

TABLE 2
key	table2_field	table2_value_field
A	X	1
A	Y	2
A	Z	3
B	P	4
B	Q	5
B	R	6
C	K	7
C	L	8
C	M	9

Concat(TABLE 2 X value * TABLE 1 A value , TABLE 2 X value * TABLE 1 B value , TABLE 2 X value * TABLE 1 C value....................TABLE 2 M value * TABLE 1 A value , TABLE 2 M value * TABLE 1 B value , TABLE 2 M value * TABLE 1 C value)

=> Concat(1*1 , 1*2 , 1*3.....7*1 , 7*2, 7*3)

=> Result: 1,2,3…..7,14,21

It happens with an alternative state in the text box. However, I cannot calculate the table expressions. I need such a solution. please help me.

Re: aggr and concat problem

Ahidhar — Wed, 06 Dec 2023 05:25:38 GMT

try this

Table2:
load * Inline
[
key,table2_field,table2_value_field
A,X,1
A,Y,2
A,Z,3
B,P,4
B,Q,5
B,R,6
C,K,7
C,L,8
C,M,9
];
left join(Table2)
Table1:
load table1_value_field;
load * Inline
[
key,table1_value_field
A,1
B,2
C,3
];

tab:
load *,
1 as Flag,
table2_value_field*table1_value_field as table_value_field
resident Table2;
drop table Table2;

tab1:
load
Flag,
concat(table_value_field,',',table_value_field) as concatenatedvalue
resident tab
group by Flag;

Re: aggr and concat problem

kuschcu66 — Wed, 06 Dec 2023 07:15:23 GMT

Thanks. I know the method in the Load Script. However, since I have millions of data, I am looking for a solution in expressions.

Re: aggr and concat problem

Ahidhar — Wed, 06 Dec 2023 11:34:53 GMT

you want the concatenated field in Table2 itself ?

Re: aggr and concat problem

kuschcu66 — Wed, 06 Dec 2023 11:50:05 GMT

Yes I want that. Thanks..

Re: aggr and concat problem

Ahidhar — Thu, 07 Dec 2023 06:27:48 GMT

Not sure if this is how you want it see if this works

Table1:
load * Inline
[
key,table1_value_field
A,1
B,2
C,3
];
Table2:
load key,table2_field,table2_value_field,1 as Flag;
load * Inline
[
key,table2_field,table2_value_field
A,X,1
A,Y,2
A,Z,3
B,P,4
B,Q,5
B,R,6
C,K,7
C,L,8
C,M,9
];
Tab:
load 0 as dum AutoGenerate 0;
for each i in FieldValueList('table1_value_field')
concatenate(Tab)
Tab1:
load
1 as Flag,
concat(table2_value_field*$(i),',') as Concate
resident Table2 ;
next i;
load Flag,concat(Concate,',') as concate
resident Tab group by Flag;
drop field dum;drop table Tab;

left join(Table2)
load Flag,concate resident Tab1;drop table Tab1;

Re: aggr and concat problem

kuschcu66 — Fri, 08 Dec 2023 09:24:35 GMT

Thank you very much Ahidhar..