topic Re: Help Please Create Pivot Table with individual fields from load script in QlikView

Help Please Create Pivot Table with individual fields from load script

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

Hi All,

I have been working with survey data trying to get a proper pivot table. Due to the fact that the each answer is a separate field I am having trouble getting a pivot table to look the way I would like. Included an example of the pivot I am able to make below, I would like to have the dimension switched with the expression. I know you can drag it dynamically, but I would like to do differences between quarters in the table, which then is not possible. My thought was to somehow make a parent category to group the necessary questions, I have been struggling with this for a while and any help would be greatly appreciated. The code for the questions I want to group is below as well.

Message was edited by: Joseph Thomas

Re: Help Please Create Pivot Table with individual fields from load script

ramasaisaksoft — Tue, 22 Mar 2016 05:59:14 GMT

Hi Joseph Thomas,

We are unable to understand your requirement.

could you pls share sample data excel sheet and the required output elaboration.

Re: Help Please Create Pivot Table with individual fields from load script

Anonymous — Tue, 22 Mar 2016 13:27:28 GMT

Hi Rama,

I added an excel book with an example of how the data is structured. Below is what I would like to eventually like to be able to build in the UI. I would like the different survey questions to be listed where the countries are in the below table.I

I am not sure if this complicates things but the data will be brought into Qlik through a API call in the future and formatting will be set up the same way as it appears in the excel book above. Thank you in advance for your help and expertise.

Re: Help Please Create Pivot Table with individual fields from load script

Anonymous — Tue, 22 Mar 2016 13:42:13 GMT

You could try using a cross table load to put all of those question columns into rows:

CrossTable([Survey Question], [Survey Answer], 5)

LOAD

ResponseID,

StartDate,

EndDate,

Finished,

OriginalResponseID,

[Please rate your level of satisfaction with the health care / facility in the following areas.-Overall facility experience],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Staff support of travelers],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Your immediate supervisor],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Your orientation],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Patient safety],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Learning new professional skills],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Manageable patient load],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Culture supporting quality/safety],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Open communication channels],

[Please rate your level of satisfaction with the health care / facility in the following areas.-Availability of adequate resources]

FROM Book1.xlsx (ooxml, embedded labels, table is Sheet2);

This will make a row for each question and create a new field called Survey Question and the corresponding answer will be in a new field called Survey Answer

Re: Help Please Create Pivot Table with individual fields from load script

Anonymous — Tue, 22 Mar 2016 15:15:22 GMT

Hi alindquist,

Thank you for the advice, I ended up changing the order in which the fields are loaded and that took care of the issue.

Best,

Joseph