Remove the old columns and update the data from updated column with (2) in the header

[AryanJ8881]

Hi, I need some help with my workflow.

My workflow is prepared in such a way that i need to update the output regularly with the new columns and delete the old columns dynamically. The problem is if i append the new columns in the dataset, they will give me a column header with (2) along the original heading. I want to keep these updated columns in my output and remove the columns with the old values with the same headers (without the "(2)").

Example: Right now i have monthly data in the output from Jan to Dec, and the input that will regularly be updated will have months Aug to Dec, so if i run the workflow, i will have original Jan to Dec and duplicate Aug to Dec with (2) in their headers. I somehow want these columns along with the old Jan to July in my final output.

So, final output should have all the columns (Jan to Jul from old data and Aug to Sep from the new data in sequence) 

Is there any solution/combination of tools for this?

Thanks in advance!

[atcodedog05]

Hi @AryanJ8881 

Can you provide some sample input and expected output it will help us get a better understanding.

[AryanJ8881]

Hi @atcodedog05 ,

I am attaching the excel file with sample data but i guess this would be the representative of the problem that i am facing.

First tab is the current output I have.

Second tab is the input that I want to use.

And the third tab is the required output.

I hope it is more clear now!

Thank you!

[atcodedog05]

Hi @AryanJ8881 

Here is how you can do it.

Workflow:

1. Using record id tool to set unique id for rows.

2. Using transpose tool converting columns to rows.

3. Using union tool merging the current and new data. Make sure to set the data order in union tool as First #1 and Second #2 like below. This data is current data followed by new data.

4. Using sample tool keep only last values for each record id and column name.

5. Using crosstab to convert it to table.

6. Using a sample attached to initial data set I am getting the column order.

7. Using union tool to set column order for the new data.

Hope this helps : )

[messi007]

@AryanJ8881,

Please see below how you can do that.

I added some comments in order to explain what I have done.

Attached the workflow,

Hope this helps!

Regards

[AryanJ8881]

@atcodedog05 , @messi007 

Thanks for the prompt response. The solutions shared by you are suitable for the excel file shared, however i figured out another problem in my original dataset.

For the new input, i have some additional data along with the old dataset. Let's say, earlier data was for 10 countries, but in the new input, now i have an additional country which i want to incorporate in my final output. 

Should the workflows shared work for that situation as well?

I am attaching the updated problem statement in this reply.

Thanks again!!

[messi007 · Accepted solution]

@AryanJ8881,

For this you have to add a union at the end 

I added a row 12 on the new input and I can see it at the end

Attached the new workflow.

Regards,

[atcodedog05 · Accepted solution]

Hi @AryanJ8881 

My above solution should work for the new scenario too. Just add a data cleanse tool to convert nulls to zero for the new country.

Workflow:

Hope this helps : )