creating Virtual Table in load script in qlik

[berryandcherry6]

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.

[lakshmikandh]

Try Loops in the Script

[berryandcherry6]

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_id

course_id

user_id

is_new_user

certificate_id

certificate_access_log_id

batch_meta_data_id

invitation_id

lender_offer_id

percentage_score

final_test_status

test_attempt_count

course_completed

final_test_appeared_date

date_completed

date_created

survey_completed

is_feedback_completed

Signup table

signup_activity_log_id

invitation_id

client_id

user_id

batch_meta_data_id

rc_activation_code

referrer_email_type_id

user_signed

user_agent

referrer

date_accessed

date_signed

ip_address

landing_page_accessed

Invitation Table

invitation_id

user_id

client_id

batch_meta_data_id

first_name

last_name

email

custom_field_1

custom_field_2

custom_field_3

Street1

Street2

city

zip_code

telephone

State

client_unique_id

rc_activation_code

shorter_activation_code

rc_activation_link

activated

date_activated

date_created

created_by

campaign_id

language_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]

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