2 Replies Latest reply: Jul 19, 2012 3:23 PM by anelsonel RSS

    Rotate Data

      Still new to Qlikview, but I need to rotate my data. For instance, I have 4 surveys with 6 or so questions per survery. I don't know how to write the script to have the data format properly for the T3-design. The goal is to use a pivot chart, Ex..Survey (Claims), Question 1. Describe your experience, Response (1), Response Value (50%). My current script to pull the data in for 1 survey see below:

       

      ClaimCloseSurvey:

      LOAD 'NOSPLIT'&'-' &

      ApplyMap('EventUser', ApplyMap('Notification', '{'&[Question 7: k]&'}', 'UNKNOWN'), 'UNKNOWN')&'-'&Date(floor([Submit Time])) & '-' & '00' as DimKey,
      [Session ID],
      Date(floor([Submit Time])) as [Submit Time],
      [Question 1: Please describe your claim experience:],
      [Question 2: Contacted within 24 hours after reporting a claim.],
      [Question 3: Received a clear explanation of the claim process.],
      [Question 4: Independent adjuster conducted a thorough investigation.],
      [Question 5: Please describe your customer service representative:],
      [Question 6: Courteous and respectful],
      [Question 6: Contact number],
      '{'&
      [Question 7: k]&'}' as [Question 7: k],
      ApplyMap('Notification', '{'&[Question 7: k]&'}', 'UNKNOWN') as EventKey,
      ApplyMap('EventReason', ApplyMap('Notification', '{'&[Question 7: k]&'}', 'UNKNOWN'), 'UNKNOWN') as EventReason
      FROM
      $(vCustomerServiceSurveysDataPath)ClaimCloseSurvey.qvd(qvd);

      It seems that I should probably concatenate the surveys, but I am at a lost. Please if anyone has any ideas, its much appreciated.

      Thanks...

       

       

       

       

       

        • Re: Rotate Data
          Stefan Wühl

          You may want to look into CROSSTABLE LOAD prefix to read your crosstable in as straight table, converting your fields Question1.. to Question 6 into field values in a common Question field. Please check the HELP for the exact syntax, I think it will look similar to

           

          ClaimCloseSurvey:

          CROSSTABLE (Question, Value, 5) LOAD

          'NOSPLIT'&'-' &

          ApplyMap('EventUser', ApplyMap('Notification', '{'&[Question 7: k]&'}', 'UNKNOWN'), 'UNKNOWN')&'-'&Date(floor([Submit Time])) & '-' & '00' as DimKey,
          [Session ID],
          Date(floor([Submit Time])) as [Submit Time],

          ApplyMap('Notification', '{'&[Question 7: k]&'}', 'UNKNOWN') as EventKey,
          ApplyMap('EventReason', ApplyMap('Notification', '{'&[Question 7: k]&'}', 'UNKNOWN'), 'UNKNOWN') as EventReason,

          [Question 1: Please describe your claim experience:],
          [Question 2: Contacted within 24 hours after reporting a claim.],
          [Question 3: Received a clear explanation of the claim process.],
          [Question 4: Independent adjuster conducted a thorough investigation.],
          [Question 5: Please describe your customer service representative:],
          [Question 6: Courteous and respectful],
          [Question 6: Contact number],
          '{'&
          [Question 7: k]&'}' as [Question 7: k]
          FROM
          $(vCustomerServiceSurveysDataPath)ClaimCloseSurvey.qvd(qvd);

           


          Then you can use the field Question as dimension in your pivot table.