topic Re: Combine fields into a single field in QlikView

Combine fields into a single field

richard_chilvers — Mon, 20 Jun 2016 08:59:37 GMT

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 — Mon, 20 Jun 2016 09:39:36 GMT

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 — Mon, 20 Jun 2016 09:43:39 GMT

Hi,

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

WHERE Len(Trim(SportsLiked))<>0

GROUP BY ID;

richard_chilvers — Mon, 20 Jun 2016 10:33:21 GMT

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 — Mon, 20 Jun 2016 10:49:34 GMT

Hi,

Can you send Actual output?