Re: Using set analysis inside loop in Load editor ... - Qlik Community

berryandcherry6 · ‎2016-10-19

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,

marcohadiyanto · ‎2016-10-19

Hi Supriya

You can try like this.

Load Date,

ClientID,

sum(Invitation) as Total_Invitation

From xxxx

group by Date, ClientID;

Regards

Anonymous · ‎2016-10-19

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 · ‎2016-10-19

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 · ‎2016-10-19

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 · ‎2016-10-19

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 · ‎2016-10-19

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 · ‎2016-10-19

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 · ‎2016-10-19

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 · ‎2016-10-19

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.

Using set analysis inside loop in Load editor in qlik