topic Re: Converting multiple row values into single columns in QlikView

Converting multiple row values into single columns

grajmca_sgp123 — Mon, 18 Jan 2016 07:14:56 GMT

Hi All,

I have a requirement here where i need to convert multiple row values into single columns with some special characters

ID Status

1 AB

1 CDEF

1 GHI

1 JKLMN

2 XYZ

2 UVWX

2 PR

ID Status

1 AB // CDEF// GHI // JKLMN

2 XYZ// UVWX // PR

if we use Concat function it will concatenate all the field values . But my requirement is i need '//' also between each field value.

do we have any other solutions to resolve this issue.

Thanks

Re: Converting multiple row values into single columns

tresB — Mon, 18 Jan 2016 07:19:10 GMT

Use delimeter '//' with concat(), like:

Concat( Distinct Status, '//')

Re: Converting multiple row values into single columns

Anonymous — Mon, 18 Jan 2016 07:28:01 GMT

May be combination of Group by and Concat() like this?

Load

ID,

Concat(Status, '//')

Resident TableName

Group by ID;

Re: Converting multiple row values into single columns

maxgro — Mon, 18 Jan 2016 07:46:37 GMT

Source:

load *, rowno() as rn inline [

ID Status

1 AB

1 CDEF

1 GHI

1 JKLMN

2 XYZ

2 UVWX

2 PR

] (delimiter is spaces) ;

Load

ID,

Concat(Status, ' // ', rn) as Status

Resident Source

Group by ID;

DROP Table Source;