Merging different records into one file

[AnandKumar1]

Hi,

I've some files with me which i want to merge on some condition.

Ex- i've 2 Fund file namely 51_returns and 51_stats . Then i've some one more file called 51_IMS 

My workflow is reading both 51_returns and stats together . Another workflow is loading 51_IMS file. Both the workflows are connecting to different location and doing some different calculation .

But i'm combining both into single WF by joining based on fund id which is 51 in this case. I've attached sample files here.

I want all these input files records to be merged in one output attached.

[grazitti_sapna · Accepted solution]

Hi @AnandKumar1 , give this a try.

[Qiu]

@AnandKumar1 
How to calculate IMS_IsPortorIndex?

[AnandKumar1]

if !IsNull([BaseMktVal]) and IsNull([IMS_BaseMktVal]) then 'Both'
elseif IsNull([BaseMktVal]) and !IsNull([IMS_BaseMktVal]) then 'Index'
else [IDH_IsPortorIndex] endif

[afv2688 · Accepted solution]

Helo @AnandKumar1 ,

Does this work for you?:

Regards

[AnandKumar1]

There is one more thing.

So for input file 51_IMS has a field ISIN=EUR. This EUR should join with both 51_returns and statistics file with ISIN=Blank. And this join output should replace the EUR value which came earlier after union.

How to achieve this?

[afv2688]

Hello @AnandKumar1,

I did the changes that you were talking to. Hope this is what you were looking for.

Regards

[AnandKumar1]

Hi @afv2688 @grazitti_sapna ,

I've one more thing here.

I've attached 3 files . IMS_Input, Returns and Statistics. I need to merge these such that the matching ISIN will produce all result and unmatched ISIN should also populate but with blank IMS value.

That means Returns and Statistics value should be joined or vlookup with IMS_input value and it should look for matching ISIN. This will give full result with all IDH and IMS values.

And unmatching ISIN should also come but with blank IMS values(IMS_basemktval, IMS_totalport). IDH values should be there

Can you check how this can be achieved?