Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

Using set analysis inside loop in Load editor in qlik

Hi,

I have a requirement where i need to get counts of Invitation according to date and ClientID, for every client. Here how could i put or approach to solve this?

I usually in front end for particular client i put expression like this

SUM({$< clientID = {'15'}>}Invitation)

But i need to get for all clients present in Client table by putting in Load Editor.

How could i do this? Any kind of help , suggestion are Appreciated

Regards,

10 Replies
marcohadiyanto
Partner - Specialist
Partner - Specialist

Hi Supriya

You can try like this.

Load Date,

          ClientID,

sum(Invitation) as Total_Invitation

From xxxx

group by Date, ClientID;

Regards

Anonymous
Not applicable

Hi,

Try this function:

1) AGGR() function

2) Group By Function at script Level

Using this function you can easily get desired output.

Regards,

Dixit

jcoggon
Partner - Contributor III
Partner - Contributor III

Hi Supriya,

You unfortunately cannot use Set Analysis in the Load Editor (Only for Front End Expressions), but you can try the following by creating a resident table that links back to your Fact table in the Load Editor.

Total_Invitations:

LOAD ClientID,

           Date,

           SUM(Invitation) AS Total_Invitations

Resident Client_Table

Group By ClientID, Date;

Kind Regards,

Jason

berryandcherry6
Creator II
Creator II
Author

HI,

Thanks for reply

But invitation data's will be in invitation table

Invitation Table

invitationID  batchID CampaignID DateCreated

2                     23             3             10/19/2016

3                     23             3             10/01/2016

4                     24             6            10/02/2016

5                     24             5             10/04/2016

6                     25             4             10/05/2016

7                     23             3            10/07/2016

ClientTable

Client ID BatchID

2                23

3                24

2                25

3                27

the logic should be such a way that it has to get invitation count for each client by batchID in invitation Table.

LIke for client ID 2 invitation count should be 3 where datecreated is is less than today. Like this i need to create a virtual table with two columns of client ID and invitationcount for all clients.

jcoggon
Partner - Contributor III
Partner - Contributor III

Hi Supriya,

Then the following will give you your desired result:

Total_Invitations:

LOAD batchID,

           DateCreated,

           COUNT(invitationID) AS Total_Invitations

Resident Invitation Table

Group By batchID,DateCreated;

Then in your front end, just sum Total_Invitations less than today.

jagan
Luminary Alumni
Luminary Alumni

Hi Supriya,

It is not possible to use set analysis in Scripting, the only way is to join the tables and use Group By.

Invitation_Temp:

LOAD

invitationID, batchID, CampaignID, DateCreated

FROM Invitation;

Left Join(Invitation_Temp)

LOAD

Client ID, BatchID

FROM Client;

InvitationCount:

LOAD

ClientID,

DateCreate,

Count(InvitationID) AS InvCout

RESIDENT Invitation_Temp;

DROP TABLE Invitation_Temp;

Hope this helps you.

Regards,

Jagan.

berryandcherry6
Creator II
Creator II
Author

Hi,

I am actually trying to achieve this kind of virtual table 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 of Signup)    

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

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

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

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


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.


According to replied answer i tried below code, but gives me data load error, and tell me to correct error



Table1:

LOAD `invitation_id` as sample_invitation_id,

    `user_id` as sample_user_id,

    `client_id` as sample_client_id,

    `batch_meta_data_id` as sample_batch_meta_data_id,

     date(floor(date_created),'MM-DD-YYYY') as sample_Date, 

     `campaign_id` as sample_campaign_id;

SQL SELECT `invitation_id`,

`user_id`,

    `client_id`,

    `batch_meta_data_id`,

    `date_created`,

    `campaign_id`

FROM rcdbprod.INVITATION WHERE client_id in (6,12,13,15,16,3,8,10,18,19,20,21,22,23);

Left Join(Table1)

LOAD

    date(floor(date_accessed),'MM-DD-YYYY') as sample_date_accessed,

    `batch_meta_data_id` as sample_batch_meta_data_id,

   date(floor(date_signed),'MM-DD-YYYY') as sample_date_signed;

SQL SELECT

    `date_signed`,

    `batch_meta_data_id`,

    date_accessed

FROM rcdbprod.SIGNUPACTIVITYLOG WHERE client_id in (6,12,13,15,16,3,8,10,18,19,20,21,22,23);

InvitationCount:

LOAD

sample_client_id,

sample_batch_meta_data_id,

Count(sample_Date) AS InvCout,

Count(sample_date_signed) AS CompletnCout

RESIDENT Table1;

DROP TABLE Table1;




Please help me on this.


jagan
Luminary Alumni
Luminary Alumni

Hi Supriya,

Why can't you do this in Front end?  It is very easier using Set analysis.  For the previous script you have to use Group by like below

InvitationCount:

LOAD

sample_client_id,

sample_batch_meta_data_id,

Count(sample_Date) AS InvCout,

Count(sample_date_signed) AS CompletnCout

RESIDENT Table1

Group by sample_client_id, sample_batch_meta_data_id;

Regards,

Jagan.

berryandcherry6
Creator II
Creator II
Author

Hi Jagan,

Thanks for reply.

There is a reason for this,we are using nprinting tool, for that we have to send table with column of direct values.So we are trying to achieve this.

please guide me through this to create virtaul table as shown above, Any suggestion or idea will be valuable.