SUM data in Load script and keep in FactTable

[berryandcherry6]

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

[saurabhwadhwa]

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]

Hi,

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

Regards,

Rahul Thakkar

[berryandcherry6]

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]

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