topic How do I aggregate a string in QlikView

How do I aggregate a string

Anonymous — Tue, 10 May 2011 18:37:51 GMT

I guess I am looking for a function with which I can flatten multiple rows to single row and multiple columns.

Input:

Name Guarantor

Sally Tom

Sally Dick

Sally Joe

Sue Harry

Output:

Name Guarantor1 Guarantor2 Guarantor3

Sally Tom Dick Joe

Sue Harry

I need this in a straight table as pivot table is not an option.

See attached for my effort which gave limited results.

Thanks,

Dinesh.

How do I aggregate a string

Clever_Anjos — Tue, 10 May 2011 19:31:27 GMT

Use the concat() function inside your load script grouping by "Name".
Example:

LOAD

Name,
CONCAT(Guarantor,'/') AS GuarantorConcatenated
RESIDENT <yourtable>

and inside your straight table you can use SUBFIELD to split again in columns

How do I aggregate a string

Tue, 10 May 2011 19:32:40 GMT

Hi! Take a look at concat (search for it in the help(F1)).

Returns the aggregated string concatenation of all values of expression iterated over a number of records as defined by a group by clause. Each value may be separated by the string found in delimiter. The order of concatenation may be determined by sort-weight. Sort-weight should return a numeric value where the lowest value will render the item to be sorted first. If the word distinct occurs before the expression, all duplicates will be disregarded.

Re: How do I aggregate a string

Wed, 16 Jul 2014 11:51:24 GMT

How can I achieve this in the calculated dimension of a straight table ?

Thanks in advance!!