3 Replies Latest reply: Oct 24, 2016 3:35 AM by Lakshmikandh Karthikeyan RSS

    creating Virtual Table in load script in qlik

    Supriya R

      Hi,

      Requirement:

      To create a table as shown below from multiple table datas for all clients.

      yes, i know this can be done in front end using expression, but my requirement is to create like this below table in script and use column of table, directly for some other purpose.

       

      I am  trying to achieve this kind of  table in script by fetching datas from different tables in script.

       

      ClientID  BatchID   Type_of_Count             CurrentMonth_Count                   PreviousMonth_Count

         15           23         Invitation                    (Total_Counts of invitation)             (Total_Counts of invitation)

      15               23         Signup                        (Total_Counts of Signup)             (Total_Counts ofSignup)   

      15               23         Course_Completion    (Total_Counts of Completion)             (Total_Counts ofCompletion)

      15              24         Invitation                    (Total_Counts of invitation)             (Total_Counts of invitation)

      15               24         Signup                        (Total_Counts of Signup)             (Total_Counts ofSignup)  

      15               24         Course_Completion    (Total_Counts of Completion)             (Total_Counts ofCompletion)


      Like above for all clients and respective Batches


      For invitation count - i have to take date_created count  from Invitation Table

      For Signup count - i have to take date_signed count  from Signup Table

      For Course_Completion count - i have to take date_completed count and final_test_statue = passed from Course_CompletionTable.


      I have attached three tables data csv file.


      Any idea or suggestion is highly appreciated.

          • Re: creating Virtual Table in load script in qlik
            Supriya R

            HI,

            thanks for reply

             

            I went through provided link, got some idea on how to loop on single table

             

            My Using Tables, currently consists of following columns.

             

            CourseCompletion Table

            course_completion_idcourse_iduser_idis_new_usercertificate_idcertificate_access_log_idbatch_meta_data_idinvitation_idlender_offer_idpercentage_scorefinal_test_statustest_attempt_countcourse_completedfinal_test_appeared_datedate_completeddate_createdsurvey_completedis_feedback_completed

            Signup table

            signup_activity_log_idinvitation_idclient_iduser_idbatch_meta_data_idrc_activation_codereferrer_email_type_iduser_signeduser_agentreferrerdate_accesseddate_signedip_addresslanding_page_accessed

             

            Invitation Table

            invitation_iduser_idclient_idbatch_meta_data_idfirst_namelast_nameemailcustom_field_1custom_field_2custom_field_3Street1Street2cityzip_codetelephoneStateclient_unique_idrc_activation_codeshorter_activation_coderc_activation_linkactivateddate_activateddate_createdcreated_bycampaign_idlanguage_id

             

            Things i need to know:

            1.) As you can see in course completion table, there it only consists of batch_meta_data_id, for this while building table i need to identify its client in looping to which it belongs, how to do this?

            2.)Using loop technique, i only find looping of single tables, how could i loop througn multiple tables data, How should i approch this?

            3.)Through looping i need to get count or sum of filtered datas, how to get sum or count of those?

             

            Could you provide some outline code for this, as i found only single table data looping every where. How should i approach to get required table to achieve requirement. Please help me on this.

             

             

            Regards,

            Supriya