Get counts for each unique fieldvalues from multiple tables.

[berryandcherry6]

Hi,

Table1:

Batch_id	Invitation_id	Comm_type_id
2	132	22
3	134	22
3	135	23
2	136	23
3	137	23
3	138	24

Table 2:

Batch_id	Invitation_id	Comm_type_id	User_signed
2	132	22	1
3	135	23	1
3	137	23	1
4	222	22	0
2	136	23	1
3	138	24	0
3	134	22	1

From above tables, i want to get all  Comm_type_id  from Table1, and match invitation_id, Batch_id in Table2 where User_signed = 1 for each comm_type_id, find sum and append it to Table1

I want to create table like this

Batch_id	Invitation_id	Comm_type_id	sum(user_signed)
2	132	22	1
3	134	22	1
3	135	23	2
2	136	23	1
3	137	23	2
3	138	24	0

How could i do this? Let me know if you need more info on this.

[berryandcherry6]

Hi,

i have not yet got any solution, for my question

[berryandcherry6]

I am using qliksense, can you attach qvf file?

[balar025]

You can use script directly.

LOAD Batch_id&Comm_type_id as KEY

,Batch_id

    ,Invitation_id

,Comm_type_id

FROM

[https://community.qlik.com/thread/270561]

(html, codepage is 1252, embedded labels, table is @1);

LOAD Batch_id&Comm_type_id as KEY

    ,sum( User_signed) as User_signed

FROM

[https://community.qlik.com/thread/270561]

(html, codepage is 1252, embedded labels, table is @2)

group by Batch_id,Comm_type_id;

[berryandcherry6]

In given code there is no join between two tables, can you explain how this works for my query?