Creating a list in a field

[nickmarlborough]

Hi, 

I have a table in the load editor at the moment as so....

[CITY],

[RESIDENTS],

[PET],

[CAR]

and the table could look like:

CITY	RESIDENT	PET	CAR
XXX	nick	dog	VW
XXX	-	cat	-
XXX	sophie	-	Porsche
YYY	ed	dog	VW
YYY	manny	snake	VW
ZZZ	lucy	-	-

i would like it so the city is per row... and the columns have the list distinct of each column

CITY	RESIDENT	PET	CAR
XXX	nick , sophie	dog , cat	VW , porsche

Would anyone be able to help 🙂 

[lennart_mo]

Hi @nickmarlborough,

I think what you're looking for is the concat()-function, which allows you to combine string values as well as set a delimiter and a sort weight.

So your Load Script should look something like this, based on your example:

Load
[CITY],
concat(DISTINCT [RESIDENT], ' , ') as [RESIDENT],
concat(DISTINCT [PET], ' , ') as [PET],
concat(DISTINCT [CAR], ' , ') as [CAR]
From [YourDataSource]
Group By [CITY];

 Let me know, if this worked for you!

[nickmarlborough]

Thank you!

lets assume i have another two columns [Y/N] and [REGION]

how could i make it so the its RESIDENT [TABLE] where [REGION] contains 'Europe' AND [Y/N] equals Y

thanks 

nick

[lennart_mo]

Do you want the Fields to appear in the new table as well or do you just want to exclude data that doesn't match your criteria? If the latter is the case just use a WHERE statement.

Load
[CITY],
concat(DISTINCT [RESIDENT], ' , ') as [RESIDENT],
concat(DISTINCT [PET], ' , ') as [PET],
concat(DISTINCT [CAR], ' , ') as [CAR]
From [YourDataSource]
WHERE Index([REGION],'Europe')>0 and [Y/N]='Y'
Group By [CITY];