Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
How can I achieve this in the calculated dimension of a straight table ?
Thanks in advance!!