Deriving values from another table based on Date Range

[Venkatasai541]

Hi Guys,

I have two tables as follows:

Table 1 - Entries posted by Users

Document Number	Posting Date	User Name
1900000000	01/04/2019	1190
1900000001	20/04/2019	1190
1900000002	01/04/2019	5163
1900000003	10/05/2019	5163
1900000004	01/04/2019	1196
1900000005	25/04/2019	1196
1900000006	10/05/2019	1196

Table 2 - User Type Changes

User	Date	From Value	To Value
1190	15/04/2019	A	B
5163	30/04/2019	B	A
1196	15/04/2019	B	A
1196	01/05/2019	A	B

I want a new column in table 1 as "User Type" and bring the same from Table 2 as "A" or "B" based on the applicable date range.

Output Table should be as follows:

Document Number	Posting Date	User Name	User Type
1900000000	01/04/2019	1190	A
1900000001	20/04/2019	1190	B
1900000002	01/04/2019	5163	B
1900000003	10/05/2019	5163	A
1900000004	01/04/2019	1196	B
1900000005	25/04/2019	1196	A
1900000006	10/05/2019	1196	B

Does anyone have a solution to this?

(Attached the input files for your reference)

[ChrisTX]

Here are a few related posts:

Alteryx Weekly Challenge #1: Join to Range

It is a good example to show the usefulness of the generate rows tool.

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-1-Join-to-Range/td-p/36621

Using Advanced Join macro

https://community.alteryx.com/t5/Engine-Works-Blog/Advanced-Join-Macro/ba-p/2355

Community > Designer > Browse Knowledge > Advanced Join: value from one file is between

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Advanced-Join-value-from-one-file-is-between...

Using Advanced Join Macro for Large Data Set

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-Advanced-Join-Macro-for-Large-Da...

[echuong1]

You can use a Join tool to bring over the From, To, and Date values. From there, you can use a formula tool to determine if the To or From value should be used by comparing the dates.

See attached for an example. Let me know if this works for you!

[Venkatasai541]

@ChrisTX, thanks for your links.

@echuong1, thanks! Your approach works only if there is a single user type change instance. In the given example, for user "1196" (multiple user type change instances) the required output should be:

Document Number	Posting Date	User Name	User Type
1900000004	01/04/2019	1196	B
1900000005	25/04/2019	1196	A
1900000006	10/05/2019	1196	B

Whereas as per your workflow, it is coming as:

Document Number	Posting Date	User Name	User Type
1900000004	01/04/2019	1196	B
1900000004	01/04/2019	1196	A
1900000005	25/04/2019	1196	A
1900000005	25/04/2019	1196	A
1900000006	10/05/2019	1196	A
1900000006	10/05/2019	1196	B

-

Venkat

[Jonathan-Sherman · Accepted solution]

Hi @Venkatasai541,

I think this is what you're trying to achieve?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Venkatasai541]

@Jonathan-Sherman, thanks a lot for your support.

The workflow is working perfectly fine.

-

Venkat