Replacing DCS with Alteryx

Hi! I was wondering if anyone in the actuarial industry has any luck replacing the process of inputting life insurance customer data into a Data Conversion System (DCS) to convert them into model point form files with Alteryx? The result of this process should have maybe thousands of excel files that look like the Sample_Model_Point_Form_File. I have not been able to find a solution online that fits exactly fit the tasks I wish to complete so here i am.

The issues I am facing are:

(1) specifying a particular layout for my output

(2) separating each individual row of data into individual excel files

(3) Outputting the file gives me an error telling me to specify a sheet name despite already using '|||Sheet1' (Already checked forums but still no solution found)

(4) Some fields in the Model_Point_Form_File such as the age of entry field would have to be calculated using some of the data (from each row) from the Sample_Base_File and I'm not sure Alteryx is able to output a row of data with some having to be calculated into a new excel as shown below

Any help would be appreciated. Cheers

Sample_Base_File.xlsx

Sample_Model_Point_Form_File.csv

Output

Accepted answers

caltang

I'll show you an example of:

Scenario A: Output to one file with mutliple sheets

Scenario B: Output to many files

For jakemiroquai.yxzp

All comments

caltang

I've done so. Replacing DCS with Alteryx is a huge project and will take a domain expert to really get right. You can reach out to me if you'd like more information - but for now let's have a look at your post.

caltang

Please find my responses below:

(1) specifying a particular layout for my output
What do you mean layout? You need to export it to Prophet right? What is the ingestion type that you require?

(2) separating each individual row of data into individual excel files

You can do so by adding a Formula tool and writing the Output path to where you want to store your information. Then, in your Output Data tool, use the following config:

(3) Outputting the file gives me an error telling me to specify a sheet name despite already using '|||Sheet1' (Already checked forums but still no solution found)

What format are you outputing to? Can you screenshot the error? Usually this means that you either selected the wrong file type, used the wrong output format, or the path doesn't exist yet.

Basically, you're using one file as a mapping file to the main file right? A simple Join Tool will get you what you want. Better still, if you can calculate fields like age within the workflow itself would be more dynamic. Be careful of dates!

I'll be happy to help you out if you can provide more information or screenshots. Or if they're too private, you can use real dummy data, not "xxxx" as data...

jakemiroquai

Hi @caltang

Thank you for your prompt and insightful response.

As of now, I am still new to both Alteryx and prophet modelling and just exploring the capabilities of Alteryx. However, can you first perhaps elaborate further on how I can use the Formula and Output tools to separate the data into individual excels as shown in the updated Sample files that I have included? I would like to at least be able to separate the data I have into individual files.

Additionally, I am still confused as to why the output tool needs to be output-ed to an existing file. Is it possible to use the output tool to produce a new excel file in the format of the csv file provided for every row of data shown in the sample base file?

It would also be greatly appreciated if you could provide some material I could read up on to better carry out this task 😀

Sample_Base_File.xlsx

Sample_Model_Point_Form_File.csv

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335