Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
berryandcherry6
Contributor 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
chooco_co
Valued Contributor

Re: Using set analysis inside loop in Load editor in qlik

Hi Supriya

You can try like this.

Load Date,

          ClientID,

sum(Invitation) as Total_Invitation

From xxxx

group by Date, ClientID;

Regards

dixit_panchal1
New Contributor III

Re: Using set analysis inside loop in Load editor in qlik

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
New Contributor III

Re: Using set analysis inside loop in Load editor in qlik

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
Contributor II

Re: Using set analysis inside loop in Load editor in qlik

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
New Contributor III

Re: Using set analysis inside loop in Load editor in qlik

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.

MVP & Luminary
MVP & Luminary

Re: Using set analysis inside loop in Load editor in qlik

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
Contributor II

Re: Using set analysis inside loop in Load editor in qlik

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.


MVP & Luminary
MVP & Luminary

Re: Using set analysis inside loop in Load editor in qlik

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
Contributor II

Re: Using set analysis inside loop in Load editor in qlik

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.