Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

creating Virtual Table in load script in qlik

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.

3 Replies
lakshmikandh
Valued Contributor II

Re: creating Virtual Table in load script in qlik

berryandcherry6
Contributor II

Re: creating Virtual Table in load script in qlik

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

lakshmikandh
Valued Contributor II

Re: creating Virtual Table in load script in qlik

Hello Henric, hic‌‌  need your help on this.

Community Browser