DATA ARRANGMENT (HELP REQUIRED)

[oiciadteam]

Hello Everyone, 

Can any one help me solve the below problem. Currently my data looks below

I want the data in one line. For example to bring the 1 & 2 record in one line, based on the second column, help me out.   

[Per]

Hi @oiciadteam 

If you could provide an example of your expected output, I am sure a solution could be found. It seems that many of the fields for each POL_POLICY_NO are identical, but for those that are not, it needs to be established how to either aggregate the data (i.e. should Category 1 always take precedence or some other logic applied), or if each field should be duplicated so you get a Member_Category_1 and Member_Category_2?

[messi007]

@oiciadteam,

Please see below

you can use the unique tool and do a unique on the column 2.

Attached the workflow.

Hope that helps,

Regards,

[echuong1]

You can use a sample tool to grab one record, per unique value.

This will grab the first instance it sees. I've selected the second column as a group by, so it'll grab the first value per value.

[oiciadteam]

Hi All, 

I have attached excel file. In the excel files there are sheet 1 ) Refined - this shows what I am expecting 2) Original - This shows the original data. 

Please assist how this can be done. 

[oiciadteam]

Hi per 

I have attached the sample file and what result i am excepting 

[messi007]

@oiciadteam,

Below the workflow

You cas use unique tool or Sample tool as suggested by @echuong1 

Attached the workflow 🙂

Please make it as solved if it helps 🙂

Regards,

[Per]

Hi @oiciadteam 

Do you know how many records there can be for each POL_POLICY_NO? If more than 3-4, then this would require an iterative macro I believe. However, if there is only ever a few, then it can done through joins.

However, I am curious to understand the purpose of doing this task, as you end up with a lot of duplicated data and records with different number of fields. Can you elaborate more on how you need to use this data in the end, as perhaps there will be a better way to structure it?

[oiciadteam]

Hi Per, 

Yes, the objective is to see how many category changes for member have taken place within a policy period. 

[Per]

Hi @oiciadteam,

Thank you. I think a different approach could potentially be more useful then. If you need to know the number of category changes for each ID, and the remaining data is static (except for POL_ISSUE_DATE) then I think a simple Summarize tool can create a better overview: Below you can see the number of policies, the issue date as a concatenated string (if not needed for further data work) and the remaining fields are then NOT duplicated. I think this could be easier to work with, but take a look below