Replacing DCS with Alteryx

[jakemiroquai]

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

[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.

(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

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 😀

[caltang · Accepted solution]

I'll show you an example of:

Scenario A: Output to one file with mutliple sheets

Scenario B: Output to many files

[caltang]

What you're doing with the Formula Tool is incorrect. You are not supposed to change the existing fields that way... best to update the fields if that is what you're trying to do. You cannot do so because it could affect the outcomes due to a force change in data types or even a dependency issue. 

But for your case, I've worked with many DCS files and requests - the big problem tends to be the multiple files of mapping requirements and hard coding within DCS. It's an amalgamation of Visual Basic code and some sort of weird interface on DCS. Good thing though is that they have a data dictionary for the syntax... though not helpful when documentation is zilch. 

If you'd like to talk more, you can always reach out to me.

[jakemiroquai]

Hi Calvin, thank you so much for your help and insight on this task at hand! Your solution helped me understand how to use the formula and output tool better. In the mean time, I am going to familiarise myself better with Alteryx haha. I'll be sure to reach out to you in future regarding any Alteryx queries. Cheers

[caltang]

Glad to be of assistance! Always happy to help.