Hi, I've many files in my directory which i'm trying to join based on a condition. I've some files in one folder which has basic structure like below. Some files has only Sec_Id or isin or sip. Some files has all 3 fields. FundSec_idisinsip123637ABCUS4971VA744573AP1124US2130US27CM6040WAW567IN3133US185BA8894AK589013TKJGUS56VAM2040WW5 Then i've few files in different folders which has structure like below FundSecurity123744573AP1124US2130567US185BA8890040WW5 So i wanted to join these files based on a condition 1) that if the Security of 2nd files exist in any of Sec_id or isin or sip from 1st files. 2) If in case all Sec_id or isin or sip exist in 1st file, then security should look for exact value and then join. Only one column out of Sec_id or isin or sip will have matching values as of Security in 2nd file. Is it possible to achieve this?

Join based on condition

Hi,

I've many files in my directory which i'm trying to join based on a condition. I've some files in one folder which has basic structure like below.

Some files has only Sec_Id or isin or sip. Some files has all 3 fields.

Fund	Sec_id	isin	sip
123	637ABC	US4971VA	744573AP1
124	US2130	US27CM6	040WAW
567	IN3133	US185BA8	894AK5
890	13TKJG	US56VAM2	040WW5

Then i've few files in different folders which has structure like below

Fund	Security
123	744573AP1
124	US2130
567	US185BA8
890	040WW5

So i wanted to join these files based on a condition 1) that if the Security of 2nd files exist in any of Sec_id or isin or sip from 1st files.

2) If in case all Sec_id or isin or sip exist in 1st file, then security should look for exact value and then join. Only one column out of Sec_id or isin or sip will have matching values as of Security in 2nd file.

Is it possible to achieve this?

Workflow

Accepted answers

atcodedog05

Hi @AnandKumar1

This would be my take on the usecase. Here is how you can solve it.

Workflow:

1. On 1st file using formula tool concat [Sec_id] - [isin] - [sip] to a combo key.

2. Using find and replace tool check whether security is present in the combo key. If yes join the row.

Non matchs will be null

Hope this helps 🙂

security.yxmd

All comments

KarolinaRoza

hi @AnandKumar1 ,

To join both files I reccomned to Transpose first one, then you can join on Fund and Security from 2nd file.

As a Join anchor output you will have joined Fund, Security and Matched key (isin or sip or sec_id) - of cource you can exclude it from output if not needed.

Then I apply Union and added comments if matched or not matched.

I hope it is something you need.

Karolina

matched not matched.yxmd

atcodedog05

Hi @AnandKumar1

This would be my take on the usecase. Here is how you can solve it.

Workflow:

1. On 1st file using formula tool concat [Sec_id] - [isin] - [sip] to a combo key.

2. Using find and replace tool check whether security is present in the combo key. If yes join the row.

Non matchs will be null

Hope this helps 🙂

security.yxmd

AnandKumar1

@atcodedog05

After using your logic i'm getting lot of records . I think most of it are duplicates.

when i run for one single file its working fine. but when i use all files in directory i'm getting huge number of records.

Any idea why is that?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703