Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pnvenkat
Contributor III
Contributor III

Load Editor Aggregating by Key

I have a data set that looks like this stored in big query.

ID          Object

1             A

1             B

1             C

2             A

2             D

 

I would like to load it so that it looks like this (all IDs are made into primary keys, and objects are concatenated):

ID          Objects

1            A, B, C

2            A, D

 

I've tried using Group By, Aggregate, String_Agg, and Concat but none of them are working.

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

May be try this

[objects]:
Load ID,
Concat([Object], ', ') AS Object
GROUP BY ID
;
SELECT ID, Object
FROM `db.objects`;

View solution in original post

8 Replies
sunny_talwar

Try this

LOAD ID,
  Concat(Object, ', ') as Object
From....
Group By ID;

pnvenkat
Contributor III
Contributor III
Author

I get this error: 

The following error occurred:
No qualified path for file: ***
 
If I modify your script to be:
 
[objects]:
Load ID,
Concat([Object], ', ') AS Object
FROM `db.objects`
GROUP BY ID;
 
It just runs forever
sunny_talwar

You did add the source where you are loading from, right?

image.png

pnvenkat
Contributor III
Contributor III
Author

yes it's a table in big query. I just did not add it to the example
sunny_talwar

Lol, how would I know what you are doing wrong if you won't even share all the details :). Please share the logfile or the script?

pnvenkat
Contributor III
Contributor III
Author

Here you go!
[objects]:
Load ID,
Concat([Object], ', ') AS Object
FROM `db.objects`
GROUP BY ID;
sunny_talwar

May be try this

[objects]:
Load ID,
Concat([Object], ', ') AS Object
GROUP BY ID
;
SELECT ID, Object
FROM `db.objects`;

pnvenkat
Contributor III
Contributor III
Author

It worked!! Thanks so much!