topic Re: pivot table not displaying results in QlikView

pivot table not displaying results

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello there,

I am new to Qlikview and I need your assistance to get the results of my survey answers displayed in a pivot table

I have 2 tables loaded from a database for survey questions and answers.

One with the survey question text , the survey that the question belong to and the question ID

question_text	survey_id	question_id
Do you find the steps in the documents simple and straightforward?	50efkjgbjsrrhjtrwj9873	5jksdfeharqonrtwe4377

the second table contains the answers and the question id corresponding to that answer, the unique answers id, the answer dates, the survey id that the question and answer belong to, the user email and the unique user id who responded to that survey

answer_id	answer_date	answer_text	replied_question_id	survey_id	user_details	user_survey_id
eoifjeclkmwe5673982	2018-03-19T10:59:27.204Z	Yes	5jksdfeharqonrtwe4377	50efkjgbjsrrhjtrwj9873	xyz@mail.com	9474d9d0-2b64-11e8-afa2-614a88624bdb-1521457162221
eriofmkelegy3473200e	2018-03-19T10:59:34.597Z	Thanks, it was great	5jksdfe45rqonrtwe4370	50efkjgbjsrrhjtrwj9873	abc@mail.com	9c6a0d40-2b64-11e8-82e4-d1195c5377f8-1521457175572
9574857ltymtyshe8883	2018-03-19T10:59:38.404Z	Not really	5jksdfeharqo382we4379	50efkjgbjsrrhjtrwj9873	abc@mail.com	9c6a0d40-2b64-11e8-82e4-d1195c5377f8-1521457175572
sacdapyui689343kkjdf6	2018-03-19T10:59:38.705Z	4 stars	5jksdfeharqonrtwe4390	50efkjgbjsrrhjtrwj9873	anonymous	9d9e4780-2b64-11e8-9d76-3db6a4092cf1-152145717759

What I need to do is display in a pivot table the user mail, the questions he replied to (translate the replied_question_id to question_text) and his answers

I tried this: if(replied_question_id=question_id, question_text,) but it didn't display anything.

Can you please assist how can I achieve that result?

Re: pivot table not displaying results

shreya_nadkarni — Wed, 12 Dec 2018 03:28:31 GMT

Can you post the required output.

Thanks

Re: pivot table not displaying results

santhiqlik — Wed, 12 Dec 2018 06:22:44 GMT

Hi,
Left join survey table with answers table so that you can get question text in Anwer table. Then you need not load Survey table separately.
Let me know if you have any questions on this.

--Priya

Re: pivot table not displaying results

Anonymous — Wed, 12 Dec 2018 08:48:58 GMT

@santhiqlik Thank you Priya, but it is still not working, actually it gives a blank cell.

Any other suggestion?

Re: pivot table not displaying results

Anonymous — Wed, 12 Dec 2018 08:49:31 GMT

hello @shreya_nadkarni

Sure, I need it to look like that at the end

Re: pivot table not displaying results

santhiqlik — Wed, 12 Dec 2018 09:14:27 GMT

Hi Samar,

Have you renamed replied_question_id as question_id while left joining as below. If not please try that
surveytab:
Load
*
from answertable //name & Path of your Answer table
Left Join
Load
survey_id,
replied_question_id as question_id,
question_text
from surveytable; //name & Path of survey table

Otherwise try loading it using Mapping Load as below

Map_QuestionID:

Mapping Load
survey_id & '_' & "question_id" as SurveyQues , //composite key to get question text
question_text
From SurveyTable;

Load
*,
ApplyMap('Map_QuestionID',survey_id & '_' & replied_question_id ,Null()) as question_text
From AnswerTable

Re: pivot table not displaying results

Anonymous — Wed, 12 Dec 2018 11:42:56 GMT

Hello @santhiqlik actually the question text disappeared.

I'm loading from a database using SQL statements, did I insert any incorrect iput in the script below?

Map_QuestionID:

Mapping Load
survey_id & '_' & "question_id" as SurveyQues , //composite key to get question text
question_text;
SQL SELECT`question_id`,`question_text`, `survey_id`, ......... From SurveyTable;
Load `_id` as answer_id
ApplyMap('Map_QuestionID',survey_id & '_' & replied_question_id ,Null()) as question_text
SQL SELECT `_id`, `answer_date`, `answer_text`,, .........From AnswerTable;

Re: pivot table not displaying results

santhiqlik — Wed, 12 Dec 2018 13:05:51 GMT

Hi Samar,

Please check below solution for your problem

Tab_Survey:
SQL SELECT`question_id`,`question_text`, `survey_id`, ......... From SurveyTable;

Map_QuestionID:
Mapping Load
survey_id & '_' & question_id as SurveyQues , //Removed " in question_id. it was added by mistake.
question_text
Resident Tab_Survey;

Drop table Tab_Survey;

Tab_Answer:

Load

`_id` as answer_id, //add all the required columns as we are not using * here
ApplyMap('Map_QuestionID',survey_id & '_' & replied_question_id ,Null()) as question_text;
SQL SELECT `_id`, `answer_date`, `answer_text`,, .........From AnswerTable;

Re: pivot table not displaying results

Anonymous — Wed, 12 Dec 2018 13:29:20 GMT

Hello @santhiqlik

still the same results I tried to display the question_text in a list box, it's not showing any results.

Re: pivot table not displaying results

santhiqlik — Wed, 12 Dec 2018 13:33:41 GMT

Please share the qvf or sample data in Excel if possible

Re: pivot table not displaying results

Anonymous — Wed, 12 Dec 2018 15:15:36 GMT

hello @santhiqlik ,

I have attached the sample data for you.

Thanks for all the assistance you're providing

Re: pivot table not displaying results

santhiqlik — Wed, 12 Dec 2018 16:08:05 GMT

Hi,

Please find the attached QVW for the same. I see that 2 of the question IDs (for those who answered) were missing in Question Tab. So I added those Question IDs text manually as below.

This is working perfectly for me. Try with excel first and change the source to SQL.

Re: pivot table not displaying results

Anonymous — Wed, 12 Dec 2018 16:39:37 GMT

Thanks a lot @santhiqlik but I'm running the personal edition so I would be thankful if you could show me what you did here in screenshots.

much appreciated

Re: pivot table not displaying results

santhiqlik — Wed, 12 Dec 2018 17:20:45 GMT

Here you go..

Re: pivot table not displaying results

Anonymous — Thu, 13 Dec 2018 10:35:31 GMT

Thank you @santhiqlik but unfortunately still the question text is not appearing

I'll send you the script I wrote for you to check.