topic MySQL Group_Concat Expression in QlikView? in QlikView

MySQL Group_Concat Expression in QlikView?

Fri, 25 Jan 2013 07:59:57 GMT

Hi @ all,

i need something similar to the group_concat sql expression from mysql (i think .. ) ..

I have following Data:

Material_Data:

LOAD * INLINE [

CompanyID, MaterialID, MaterialNameIndex, MaterialName

1,1001,1,Pencil

1,1001,2,5mm

1,1001,3,black

1,1002,1,Shovel

1,1002,2,white

1,1003,1,Pencil

1,1003,2,3.6mm

1,1003,3,blue

2,1001,1,Rubber

2,1001,2,rubs everything

2,1001,3,and nothing

2,1002,1,Powder-Gun

2,1002,2,one of the big ones

]

and want to Serialize it.

1,1001,Pencil 2,5mm black

1,1002,Shovel white

1,1003,Pencil 3.6mm blue

2,1001,Rubber rubs everything and nothing

2,1002,Powder-Gun one of the big ones

Thanks very much for your help!

ps.: Sorry if there is a similar post, i searched for a similar solution, but i am too dumb to find it.

Re: MySQL Group_Concat Expression in QlikView?

Fri, 25 Jan 2013 09:27:24 GMT

Hi,

you can do that with this script (concat statement does the job):

LOAD * INLINE [

CompanyID, MaterialID, MaterialNameIndex, MaterialName

1,1001,1,Pencil

1,1001,2,5mm

1,1001,3,black

1,1002,1,Shovel

1,1002,2,white

1,1003,1,Pencil

1,1003,2,3.6mm

1,1003,3,blue

2,1001,1,Rubber

2,1001,2,rubs everything

2,1001,3,and nothing

2,1002,1,Powder-Gun

2,1002,2,one of the big ones

];

inner join

load CompanyID, MaterialID, MaterialNameIndex, CompanyID & '-' & MaterialID as key Resident Material_Data;

load key, concat(MaterialName, ' ', RecNo()) resident Material_Data group by key;

Re: MySQL Group_Concat Expression in QlikView?

Gysbert_Wassenaar — Fri, 25 Jan 2013 09:50:44 GMT

Try:

load

CompanyID, MaterialID,concat(MaterialName, ' ',MaterialNameIndex) as Materials

from ...mysourcetable...

group by CompanyID, MaterialID;

Re: MySQL Group_Concat Expression in QlikView?

Fri, 25 Jan 2013 10:38:05 GMT

Amazing! Thanks guys!

That helped me a lot!

i never thought it is so easy, .. but i had my problems with the load concat() .. thing..

THANK YOU!