Concatonate Rows and select Columns

[davidgosselin]

Hello, 

It would be great if someone could help me with a question for my data set. As you can see in my sample file, I have a data set with to header rows. The only columns I need are the ones where the second header is 'Reporting Total' for each countries. One thing that I have tried to use to help me is that for every country, the data that I want('Reporting total') is the fifth row from the country. 

I have search various different ways of doings this but I haven't found a way since I have 200 countries and don't want to manually select all of them for each files of data I have.

Also, I would like to delete de second row as it is not needed once I have the columns selected('reporting total')

It would be very appreciated if someone could help me.

Thank you in advance,

[grossal]

Hi @davidgosselin,

I got you!

Workflow:

Output:

What happens:

1) Shift all columns (other than first) to rows (Transpose)

2) Filter all rows with "Reporting Total"

3) Join all rows based on the names of the "Reporting Total" rows back together

4) Remove the numbers from the Country names (Data Cleansing)(they get numbers, because of the Transpose tool)

5) Shift them back to columns (Cross Tab)

Worfklow is attached. Let me know if this solved the problem.

Best

Alex

[AbhilashR]

@davidgosselin - Attached is my attempt to retain only the Reporting Total columns. Hope this helps.

[davidgosselin]

Thank you Alex for your quick response ! 

It almost solves my problem completly.

I have one more issue:

-When we remove the numbers from the "country names", this creates an issue for me since the so call "Country Names" are not countries in my data input, but entities. The problem I have when I remove the numbers automatically given is that another problem I have in my workflow is that i want to keep only certain Entities. The entities I would like to keep are the ones containing numbers in their name since they are the only relevant ones.

In summary: I have hundreds of entities, the only ones I want to keep are the ones that have numbers in them(which I am not sure how to do) and if I remove the numbers it will be hard for me to find a way to keep the ones with numbers in them.

For example:

I would like to keep the entity(and its entire column with data) with the name: Entity12holding

I would like to discard the entity(and its entire column with data) with the name: Entityholding

I reuploadeed my sample and desired output and included a number so that it is more clear what i would want.

Thank you for your time,

[grossal]

I think I fixed it @davidgosselin.

I replaced the Data Cleansing Tool with a formula and use a Regex_Replace function to remove the last number and an _ before it.

Workflow attached.

Best

Alex

[davidgosselin]

Thank you so much! And the last thing, do you know how I could filter to only keep the entities who have numbers in them? In my sample, I would only keep Canada1(the numbers are not always at then end of the entities names) so I would need to find a way to filter if there is a number or not in the name

Thank you for your time,

Dave

[grossal · Accepted solution]

Oh sorry, I read over that.

I added a Filter with a Regex_Match for getting only the columns with at least one number in it. Workflow attached.

I am happy to help.

Alex

[davidgosselin]

Thank you!