Combine fields into a single field

[richard_chilvers]

My database contains information on survey results, with fields such as:

SportsLiked

Sport1

Sport2

Sport3

Sport4

The fields Sport1, Sport2, etc. may or may not be blank.

When I LOAD this data, I want to generate a single field which contains all the non-blank sports, separated by a delimiter.

Then in QlikView my table would have a field called SportsLIked, and contents such as 'Cricket;Tennis;Hockey'.

Any neat ideas please ?

Thanks

[swuehl]

Maybe something like this using a CROSSTABLE LOAD prefix:

CROSS:

CROSSTABLE (SportsLikedFieldName, SportsLikedFieldValue,2)

LOAD SurveyID, SportsLiked, Sport1,Sport2,Sport3,Sport4 //Could contain a lot more fields

FROM ....;

RESULT:

LOAD SurveyID, Concat( SportsLikedFieldValue, ',') as SportsLikedGrouped

WHERE Len(Trim(SportsLikedFieldValue))

GROUP BY SurveyID;

[muthukumar77]

Hi,

LOAD ID, Concat(Distinct SportsLiked, ',') as SportsLiked

WHERE Len(Trim(SportsLiked))<>0

GROUP BY ID;

[richard_chilvers]

Hi Muthukumar

Thanks for your response.

In my data, there is only a single record for each ID, with all the sports on that record, so GROUP CONCAT won't work

[muthukumar77]

Hi,

Can you send Actual output?