topic Re: Query Help in QlikView

Query Help

Anonymous — Thu, 08 May 2014 19:22:37 GMT

Can anyone give me some help with this query? I’m trying to concatenate multiple rows from a SQL Server table into a single row, for every key.

Example Table

Key Comments

Order1 This is the initial comment record.

Order1 This is the second comment.

Order1 Third comment.

Order2 Here is a comment.

Order2 Final comment.

Desired Result

Key Comments

Order1 This is the initial comment record.; This is the second comment.; Third comment.

Order2 Here is a comment.; Final comment.

Re: Query Help

its_anandrjs — Thu, 08 May 2014 19:34:52 GMT

Use Concat key word

See the below load script

===================================

Source:

LOAD * INLINE [

Key, Comments

Order1, This is the initial comment record.

Order1, This is the second comment.

Order1, Third comment.

Order2, Here is a comment.

Order2, Final comment.

];

FinalTable:

LOAD

Key,

Concat(Comments,';') as Comments

Resident Source

Group By Key;

Drop Table Source;

Re: Query Help

Thu, 08 May 2014 19:35:10 GMT

Try this;

Load Key, Concat(Comments,';') as Comments

From Table

Group By Key;

Thanks

Re: Query Help

Anonymous — Thu, 08 May 2014 19:57:35 GMT

Well, that was ridiculously simple. My problem was thinking in SQL instead of QlikView. Thanks!

Re: Query Help

its_anandrjs — Thu, 08 May 2014 20:08:50 GMT

Same as Concat key word you can use in the SQL but i believe you are not able to add symbol

SELECT CONCAT ( Field1, Field2) AS Result;

Re: Query Help

anbu1984 — Fri, 09 May 2014 05:49:31 GMT

In Sql, you can try this

Select Stuff(

(

Select ', ' + T2.Comments // Add a comma (,) before each value

From Table As T2

Where T2.Key= T1.Key

For Xml Path(''), type // Select it as XML

).value('.', 'nvarchar(max)'), 1, 2, '') // This is done to remove the first character (,) from the result

From Table As T1

Group By T1.Key