Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi,
LOAD ID, Concat(Distinct SportsLiked, ',') as SportsLiked
WHERE Len(Trim(SportsLiked))<>0
GROUP BY ID;
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
Hi,
Can you send Actual output?