Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

Help Please Create Pivot Table with individual fields from load script

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.

Capture1.PNGCapture.PNG

Message was edited by: Joseph Thomas

1 Solution

Accepted Solutions
Highlighted
New Contributor III

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

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

View solution in original post

4 Replies
Highlighted
Valued Contributor III

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

Hi Joseph Thomas,

We are unable to understand your requirement.

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

Highlighted
New Contributor II

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

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. Capture4.PNG

Highlighted
New Contributor III

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

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

View solution in original post

Highlighted
New Contributor II

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

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