Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

Dynamic merge all columns?

Hi,

I am looking for a way to dynamically merge all columns after the first column. I have a table that will have a different number of columns based on the file selected in the file path, and I need columns 2 and on to merge. The files that are selected in the file path are unique from one another, so I need the merge to happen in a dynamic fashion.

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Altair Forum User

Chase,

You're not giving us much to go on in terms of the type of source file or how the "columns" are identified.

There will be at least one technique for achieving your objective - maybe several approaches.

We could offer a consultancy arrangement for you to work through a solution without exposing any sensitive data to the world on the forum. Would that be of interest?

I'm sure Datawatch could provide the same sort of support through their technical services team.

The most immediate idea would be to use a calculated field in the table to concatenate the columns but I would imagine you have maybe already considered that and found it lacking something for your needs. If so that suggests that you need to consider the detail of the challenge in order to derive the most appropriate solution.

Grant

Altair Forum User

Chase,

You're not giving us much to go on in terms of the type of source file or how the "columns" are identified.

There will be at least one technique for achieving your objective - maybe several approaches.

We could offer a consultancy arrangement for you to work through a solution without exposing any sensitive data to the world on the forum. Would that be of interest?

I'm sure Datawatch could provide the same sort of support through their technical services team.

The most immediate idea would be to use a calculated field in the table to concatenate the columns but I would imagine you have maybe already considered that and found it lacking something for your needs. If so that suggests that you need to consider the detail of the challenge in order to derive the most appropriate solution.

Grant

In addition to what Grant pointed out, what version of Monarch are you using and are you using Monarch Classic or Data prep studio?

Altair Forum User

In addition to what Grant pointed out, what version of Monarch are you using and are you using Monarch Classic or Data prep studio?

Good point Chris.

I had meant to ask that too but completely forgot to write it in!

Grant

Altair Forum User

In addition to what Grant pointed out, what version of Monarch are you using and are you using Monarch Classic or Data prep studio?

Unfortunately Monarch does not have a function which would return total number of columns. To get around that I would suggest the following workaround:

I am assume your file is delimited, and you know total number of columns that a file could have. for example one file could have 10 columns and another file could have 15 columns, but most columns a file could have is 30.

Create a file and name it 1.csv. Add 30 commas (assume delimited character is comma) in 1.csv file. This file will be your template.

Launch Monarch classic, open database, select 1.csv and a file with data. After you configure import database field, and view data in the table view, there will be one bank row, which you can filter it out, and other rows with data. Create a calculated field and concatenate column 2 thru 30. since some of the columns from 1.csv will be null, when you concatenate columns there will be no values on the merged column.

Hope that helps

Regards