Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JBQlik
Contributor
Contributor

Concatenating column data based on matching criteria

I'm very new to Qlik and would appreciate any help.  I have multiple choice survey data for several thousands of respondents being asked multiple questions.  The data looks similar to this:

RespondentIDQuestionIDResponse
000101A
000102C
000103D
000104B
000201D
000202D
000203C
000204A
000301A
000302C
000303D
000304B

 

I want to concatenate each Respondent's Response data based on the QuestionID order...

RespondentIDQuestionIDResponseConcatenatedResponses
000101AACDB
000102CACDB
000103DACDB
000104BACDB
000201DDDCA
000202DDDCA
000203CDDCA
000204ADDCA
000301AACDB
000302CACDB
000303DACDB
000304BACDB

 

so I can export to Excel, remove duplicate RespondentIDs, and identify how many Respondents have the exact same responses across all questions:

RespondentIDConcatenatedResponsesSameResponseCount
0001ACDB2
0002DDCA1
0003ACDB2

 

It takes way too long to export the data and have Excel process it.  Is there a way to do this in Qlik before exporting?

Thanks!

4 Replies
dilipranjith
Partner
Partner

Hi 

yes it can be done in qlik sense. see attached qvf

Script

Responses:
LOAD
    RespondentID,
    QuestionID,
    Response
FROM [lib://Downloads/Sample Table.xlsx]
(ooxml, embedded labels, table is Sheet1);

left Join (Responses)
load RespondentID, Concat(Response) As [Concatenated Response]
resident Responses
group by RespondentID;

Chart

dimensions RespondentID, Concatenated Response

measure -  Count(distinct total<[Concatenated Response]> RespondentID)

dilipranjith
Partner
Partner

JBQlik
Contributor
Contributor
Author

Thank you, Dilipranjith.  It appears I'm using Qlik View instead of Qlik Sense (I mentioned I was new).  Is there a way to do the same thing within Qlick View to build a table that's already concatenated like I laid out?

dilipranjith
Partner
Partner

You can use the same script and chart measure i shared