0 Replies Latest reply: Sep 3, 2015 8:23 AM by Kumar Pramod RSS

    pivot table creation

    Kumar Pramod

      Hi all,

      mto

      gwassenaar

       

      I have created a pivot table but facing some issue.Please any one help on this.

       

      Hi all you can observe in below image client name and batch name is not displaying in first row.

      Not able to add .qvf file and i am adding sample data.

      I am preparing pivot table using 3 different tables:

      Script i am using to load:

      LOAD `invitation_id`,

          `user_signed`,

          referrer_email_type_id as email_type_id;

      SQL SELECT `invitation_id`,

           referrer_email_type_id,

          `user_signed`

      FROM SIGNUP WHERE client_id in (6,12,13,15,16,3,8,10,18);

       

      JOIN

       

      LOAD status,

          status as email_type_id,

          `log_type_id`;

      SQL SELECT lender_offer_id,

            status ,

          `log_type_id`

      FROM USERACTIVITY where status in (2,3,4,8);

       

      LOAD `course_completion_id`,

          `invitation_id`,

          `course_completed`;

      SQL SELECT `course_completion_id`,

          `invitation_id`,

           `course_completed`

      FROM COMPLETION;

       

      pivot.png

       

      Dimensions:

       

      1.client id

      2. batchname

      3. IF(match(email_type_id, '44'), Replace(email_type_id, '44', 'InvitationEmail'),

      IF(match(email_type_id, '2'), Replace(email_type_id, '2', 'Signed-Up First Reminder'),

      IF(match(email_type_id, '3'), Replace(email_type_id, '3', 'Signed-Up Second Reminder'),

      IF(match(email_type_id, '4'), Replace(email_type_id, '4', 'Signed-Up Third Reminder'),

      IF(match(email_type_id, '8'), Replace(email_type_id, '8', 'Signed-Up Fourth Reminder'),

      IF(match(email_type_id, '45'), Replace(email_type_id, '45', 'Not Signed First Reminder'),

      IF(match(email_type_id, '46'), Replace(email_type_id, '46', 'Not Signed Second Reminder'),

      IF(match(email_type_id, '47'), Replace(email_type_id, '47', 'Not Signed Third Reminder'),

      IF(match(email_type_id, '126'), Replace(email_type_id, '126', 'Not Signed Fourth Reminder'),email_type_id)))))))))

       

       

      Measures:

      1.count(email_type_id)

      2.Count({$<user_signed = {1}>}email_type_id)

      3.Count({$<user_signed = {0}>}email_type_id)

      4.Count({$<course_completed = {1}>}email_type_id)

       

      The first 3 rows is getting the data from USERACTIVITY table. The data showing is correct. but not showing the client and batch.

      The other rows are taking data from SIGNUP  table. They are displaying properly.

       

      Please any one help on this. ask any clarification if needed.

      As i added sample/duplicate data the name of the tables and columns may vary.

       

      Regards,

      Pramod