How to Keep records in output only if they are available in another file.

[Pankhudri20]

Hello,

I am working on an extensive Alteryx workflow which has several files as input.

For example,  I have 2 files, Div and Active, If an employee ID from Div exists in Active, then only it should be included in the output file else not. I do not need any other extra columns.

I have tried join and it does not give me the required result.

Any help would be greatly appreciated. 
Unfortunately, I cannot share any files due to confidentiality.

Regards,

Pankhudri

[Luke_C]

Hi @Pankhudri20 

Just based on your description a join tool should definitely work. Understanding the confidentiality, are you able to provide sample data where this isn't working?

[Kenda]

Hi @Pankhudri20 

It sounds like the Join tool should work here. How do you have it configured? I imagine it working like this

- Join Div and Active by employee ID

- Keep only the J records, and deselect any fields from the Div input

If you have already tried this, why is the output not what you are expecting? More understanding of the issue will help us to get you what you're looking for.

[Pankhudri20]

Hello @Kenda @Luke_C 

Thank you for your quick responses.

So, I did the join and kept only the J output.

Div has approx 6600 rows and Active as 5500. However, in my output I am getting 11000 rows.

I will try and create a sample data to share.

Regards,

Pankhudri

[Kenda]

@Pankhudri20 

I see. It sounds like there are multiple records from your Div input that have the same employee ID. In this case, I would suggest using a summarize tool before the join to group by employee ID. This will give you a unique list of all of the employee ID's from your Div input that you can then join on.

Hope this helps!

[Pankhudri20]

Hello @Kenda 

Thank you for your response!

I will try the summarize tool.

Regards,

Pankhudri 

[Pankhudri20]

Hello,

I also need to extract data from odbc. But it needs to be automated and should pull the data from the previous fiscal period always. 

For example, if the workflow is running on 5th of April, it should pull data from 1st march to 4th April.

I already have a filed "FISCPER" which contains the period numbers. I want to know how can I a define a condition that it should consider the latest value and stop as soon as the period changes. This is a dynamic period and depends on the whenever the week finishes in a month.

FISCPER    Date(yyyymmdd)

2021003     20210301

.

.

.

2021003    20210404

2021004    20210405

Is there a way to specify this condition?

Regards,

Pankhudri

[Kenda]

Hi @Pankhudri20 

I apologize as I'm not sure I understand the question exactly, but hopefully this is helpful...

Below I used the multi-row formula tool to create a new field that will have a value of 0 until the FISCPER field changes. When it changes, this new field will now be 1. Then, this will allow you to add a filter tool so you can filter our the 1's and keep the 0's. 

Note: Make sure to change the dropdown for values for rows that don't exist to set to values of closest valid row. We do this so that the first row of our new field will be 0.

[Pankhudri20]

Hello @Kenda ,

Thank you so much for your quick response!

I have a doubt. So it is giving me 0 for feb and I have hypothetical data till aug.

If I want the latest data then shouldn't it give 0 for aug and 1's for all other months.

Also, when new data for September is added, will it make the 0's for aug as 1's?

Hope this is understandable.

Thanks again!

Regards,

Pankhudri

[Kenda]

In this case, I think I will recommend a different route, @Pankhudri20 

This solution will solve the problem where you're always wanting to keep only the records assigned to the second to last FISCPER.

First, use a summarize tool to get a unique list of all FISCPER values. Then, use a sort tool to sort this field in descending order. Next use a select records tool to keep only the second record. Finally, use a join tool to get the records back from the input, only keeping those that match with the one FISCPER value.