Use question labels and answer labels from data dictionary

[olivierdt]

Hi all,

I am fairly new to Qlik Sense but I have some experience with other BI tools.

I am currently running a questionnaire with multiple different forms and hundreds of participants. The data export includes 3 different files (see an example in attachment):

• The responses to each form of the questionnaire (every form is in a different file): One row per participant and each question in a different column
  • Columns are headed by a shortened "question ID" instead of the full question (e.g. "COMORBID" instead of "Do you have any comorbidities")
  • Answers to the multiple choice questions use "answer value" instead of the full answer (e.g. "2" instead of "high blood pressure")
• A question dictionary: This file shows the questions that are attached to the question IDs
• An answer dictionary: This file shows the answers that are attached to the answer values

When building a dashboard in Qlik Sense, I would like to show the actual questions and answers instead of the IDs and values. This means I have to do the following for the form responses files:

• Replace the question IDs in the column headers by the actual questions (these can be found in the question dictionary)
• Replace the answer values by the actual answer (these can be found in the answer dictionary).

I haven't found a way yet to do this automatically. Does anyone know how I could do this?

[Taoufiq_Zarra]

@olivierdt 

can you share the exprected output ?

[olivierdt]

Hi @Taoufiq_Zarra ,

Thanks for your answer!

You can find the expected output in attachment. As you can see, in Qlik Sense we would like to use the actual questions and responses (instead of the IDs and values) to make the graphs.

Does that make sense? Let me know if you have any other questions

Olivier

[Taoufiq_Zarra]

@olivierdt 

to get to that kind of output I used Generic Load with a mapping load .

the Lib folder contains the files sent

Script :

Questiondictionary:
mapping load
 [Form ID]&'_'&[Question ID] as Ccode, 
     Question
FROM
[lib://DataFolder/Question dictionary(1).xlsx]
(ooxml, embedded labels, table is [Question dictionary]);


AnswerMapping:
mapping load
  [Form ID]&'_'&[Question ID]&'_'&[Answer value] as Ccode2, 
     Answer
FROM
[lib://DataFolder/Answer dictionary.xlsx]
(ooxml, embedded labels, table is [Answer dictionary]);




TmpResponse:
LOAD [Participant ID], 
     [Form ID], 
     AGE, 
     SEX, 
     CHILDREN, 
     COMORB, 
     HOSPITAL
FROM
[lib://DataFolder/Form responses.xlsx]
(ooxml, embedded labels, table is [Form responses]);



For i = 3 to NoOfFields('TmpResponse')

  let field = FieldName(i,'TmpResponse');

  FinalTmp:
  		load 
  		[Participant ID],
        [Form ID],
		ApplyMap('Questiondictionary',Ccode,'Null') as VQuestion,
  		ApplyMap('AnswerMapping',Ccode2,'Null') as VResponse;
  		LOAD
	[Participant ID], 
	[Form ID]&'_'&'$(field)' as Ccode, 
	[Form ID]&'_'&'$(field)'&'_'&$(field) as Ccode2, 
     [Form ID],
 $(field) as Value,
  '$(field)' as FlagName

  resident TmpResponse;

next

drop table TmpResponse;


Flags:
GENERIC LOAD [Participant ID],[Form ID],VQuestion,VResponse
RESIDENT FinalTmp;


CombinedGenericTable:


Load distinct [Participant ID] resident FinalTmp;
drop table FinalTmp;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));
  //trace $(i) - '$(vTable)';
  IF WildMatch('$(vTable)', 'Flags.*') THEN 

  LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF 

NEXT i

output :

attached Qliksense file