Complicated Join of two tables

[Katiewen]

Hi everyone,

I have been trying to learn alteryx hands-on but I have been facing a few challenges.

Any help/insight would be appreciated from the alteryx community

I am trying to join 2 data sets to create a report where in the below criteria’s should be met

1. To check if a particular code present in Table 1 is present in Table 2 for a particular month if yes then (New Column 1) is populated with present else populate with Absent
2. Calculate for a particular code in Table 1 the number of days, for example, for code 201 there can been seen fields populated from 1st to 4th Jan, the New Column 2 is populated with 4
3. Check for a particular code in Table 1, the number of times mode is taken as R, for example, for code 201, 3 instances can been seen wherein mode is R hence New Column 3 is populated with 3.

Table 1

Code	Name	Code_1	category	city	profile	location	Region	mode	date	timestamp details
201	A	1001	Phone	M	ACT	US	North	R	1-jan	10:00:00, 10:17:00, 10:26:00
201	A	1001	Phone	M	ACT	US	North	B	2-Jan	11:30:00, 10:00:00
201	A	1001	Phone	M	ACT	US	North	B	3-Jan	18:45:00
201	A	1001	Phone	M	ACT	US	North	R	3-Jan	13:23:00
201	A	1001	Phone	M	ACT	US	North	R	4-Jan	12:24:00
202	B	1001	Phone	M	ACT	US	North	R	1-jan	10:00:00, 10:17:00, 10:26:00
202	B	1001	Phone	M	ACT	US	North	B	2-Jan	11:30:00, 10:00:00
202	B	1001	Phone	M	ACT	US	North	B	3-Jan	18:45:00
202	B	1001	Phone	M	ACT	US	North	R	3-Jan	13:23:00
202	B	1001	Phone	M	ACT	US	North	R	4-Jan	12:24:00
203	C	1002	Phone	O	ACT	US	South	R	1-jan	10:00:00, 10:17:00, 10:26:00
203	C	1002	Phone	O	ACT	US	South	B	2-Jan	11:30:00, 10:00:00
203	C	1002	Phone	O	ACT	US	South	B	3-Jan	18:45:00
203	C	1002	Phone	O	ACT	US	South	R	3-Jan	13:23:00
203	C	1002	Phone	O	ACT	US	South	R	4-Jan	12:24:00

Table 2

Month	Process	Code_1	Code	name	city	Region	Cost
Jan-21	R	1001	201	A	M	North	100
Jan-21	R	1001	202	B	M	North	200
Jan-21	R	1002	203	C	O	South	300
Jan-21	R	1004	204	D	N	West	400
Jan-21	R	1004	205	E	N	West	500

Desired Output

Month	Region	City	Code_1	Code	name	New column1	New Column 2	New Column 3	Cost
Jan-21	North	M	1001	201	A	Present	4	2	100
Jan-21	North	M	1001	202	B	Present	4	2	200
Jan-21	South	O	1002	203	C	Present	4	2	300
Jan-21	West	N	1004	204	D	Absent	0	0	400

[gabrielvilella]

Hi @Katiewen, here is one way of doing this.