Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
richard_chilvers
Specialist
Specialist

Combine fields into a single field

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

4 Replies
swuehl
MVP
MVP

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
Partner - Creator III
Partner - Creator III

Hi,

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

WHERE Len(Trim(SportsLiked))<>0

GROUP BY ID;

Muthukumar Pandiyan
richard_chilvers
Specialist
Specialist
Author

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
Partner - Creator III
Partner - Creator III

Hi,

Can you send Actual output?

Muthukumar Pandiyan