Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Valued Contributor

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
Highlighted
MVP
MVP

Re: Combine fields into a single field

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;

Highlighted
Partner
Partner

Re: Combine fields into a single field

Hi,

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

WHERE Len(Trim(SportsLiked))<>0

GROUP BY ID;

Muthukumar Pandiyan
Highlighted
Valued Contributor

Re: Combine fields into a single field

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

Highlighted
Partner
Partner

Re: Combine fields into a single field

Hi,

Can you send Actual output?

Muthukumar Pandiyan