Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

SUM data in Load script and keep in FactTable

Hi,

i need to get sum of data in join table and keep it in Fact table, for that i have used below code

SIGNUPACTIVITYLOG:

LOAD  `invitation_id`,

    `user_id`,

    `client_id`,

    `batch_meta_data_id`,

    `referrer_email_type_id`,

    date_accessed,

    `user_signed`;

SQL SELECT

    `invitation_id`,

    `client_id`,

    `user_id`,

    `batch_meta_data_id`,

    `referrer_email_type_id`,

    `user_signed`,

    date_accessed

FROM MyDB.SIGNUPACTIVITYLOG;

left join (SIGNUPACTIVITYLOG)

LOAD

    `invitation_id`,

    `lender_offer_id`,

    `final_test_status`;

SQL SELECT

    `user_id`, 

    `batch_meta_data_id`,

    `invitation_id`,

    `lender_offer_id`,

    `final_test_status`

FROM MyDB.COURSECOMPLETION;

Final:

LOAD  `invitation_id`,

    `user_id`,

    `client_id`,

    `batch_meta_data_id`,

    `referrer_email_type_id`,

    `user_signed`,

     `lender_offer_id`,

    `final_test_status`,

    date_accessed,

    Count(date_accessed) as LandingCount

    Resident SIGNUPACTIVITYLOG Group by client_id,batch_meta_data_id,referrer_email_type_id, invitation_id,user_id,final_test_status,user_signed,lender_offer_id, date_accessed;

    drop Table SIGNUPACTIVITYLOG; 

So in final table i have count of date_accessed for particular referrer_email_type_id with respective to client . Now, if i put straight table using dimension like  client_id,  batch_meta_data_id , referrer_email_type_id and  LandingCount (put as dimension). Here i am not getting total count for each referrer_email_type_id  with respect to client_id,  batch_meta_data_id. What i am doing wrong.


let me know if you need more info on this? PLease help me on this

4 Replies
saurabhwadhwa
Partner
Partner

Hi Supriya ,

Why r you putting LandingCount as dimension ? If you put it as dimension it will behave as a dimension and it won't sum up .

Please share the expected output and current output which you are getting,that would be helpful to achieve the expected O/p.


BR,

Saurabh

thakkarrahul01
Creator
Creator

Hi,

Please use Sum(LandingCount) as a measure. If you are trying to validate loaded data, please use Table box.

Regards,

Rahul Thakkar

berryandcherry6
Creator II
Creator II
Author

Hi all,

Yes, you are right. But i want to prepare a Final Table in script itself, which contains sum i.e Count(date_accessed) as LandingCount.

LandingCount sum has to be grouped by client_id,batch_meta_data_id,referrer_email_type_id .


Please let me know what i am missing or not right in query.

OmarBenSalem
Partner
Partner

to have your landingCount working properly in your charts (table), you have to use it as a measure as :

SUM (LandingCount)

Qlik won't treat a measure as a measure without using an aggregation function even if it has been calculated in the script