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