topic Re: How do I Group Text Values Together from Multiple Records in QlikView

How do I Group Text Values Together from Multiple Records

patbuchanan — Tue, 25 Nov 2014 18:51:05 GMT

I have a table that has multiple records for and ID based on how many Rooms are associated to that ID and I'm trying to present one record for each ID that has all the Rooms associated with the ID combined in one field.

So, I'm trying to convert:

To:

Re: How do I Group Text Values Together from Multiple Records

simenkg — Tue, 25 Nov 2014 18:58:08 GMT

Depends on where you want to do this.

In script:

Load ID, Concat(distinct Room,',') as [Room(s)]
From Table.qvd (qvd)
group by ID;

In chart.

Dimension: ID
Expression: Concat(distinct Room,',')

Regards

SKG

Re: How do I Group Text Values Together from Multiple Records

maxgro — Tue, 25 Nov 2014 18:59:26 GMT

load ID, concat(Room, ', ') as Rooms

from yoursourcetable

group by ID;

Re: How do I Group Text Values Together from Multiple Records

JonnyPoole — Tue, 25 Nov 2014 19:00:01 GMT

Worth putting a 'distinct' in there so it doesn't repeat values (if that is a possibility)

Concat( distinct Room,',')

Re: How do I Group Text Values Together from Multiple Records

simenkg — Tue, 25 Nov 2014 19:02:55 GMT

I agree. I will edit my response.

Re: How do I Group Text Values Together from Multiple Records

ashfaq_haseeb — Tue, 25 Nov 2014 19:07:04 GMT

Try this

Load ID,Concat(Room,',') as Rooms Group by ID;

Load * Inline

[

ID,Room

100,Room1

100,Room3

101,Room1

101,Room2

];

Regards

ASHFAQ

Re: How do I Group Text Values Together from Multiple Records

Tue, 25 Nov 2014 23:40:40 GMT

HI Pat hope you help this

Try this both

In script:

Load ID, Concat(distinct Room,',') as [Room(s)]
From Table.qvd (qvd)
group by ID;

Load ID,Concat(Room,',') as Rooms Group by ID;

Load * Inline

[

ID,Room

100,Room1

100,Room3

101,Room1

101,Room2

];