Discussion Board for collaboration related to QlikView App Development.
My database contains information on survey results, with fields such as:
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 ?
Maybe something like this using a CROSSTABLE LOAD prefix:
CROSSTABLE (SportsLikedFieldName, SportsLikedFieldValue,2)
LOAD SurveyID, SportsLiked, Sport1,Sport2,Sport3,Sport4 //Could contain a lot more fields
LOAD SurveyID, Concat( SportsLikedFieldValue, ',') as SportsLikedGrouped
GROUP BY SurveyID;
LOAD ID, Concat(Distinct SportsLiked, ',') as SportsLiked
GROUP BY ID;
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
Can you send Actual output?