community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Macro Output Dropping Data

Meteoroid

Hi talent people in the community, I have been using Alteryx for some time but rarely use macro to do anything. I wrote a new macro yesterday to help me loop through a list of attributes in a filter. Everything works fine in the macro. I even put a browse to look at my result and it is exactly what I want. Below is an excerpt:

 

macro.PNG

 

Notice that 2015 - 2018 records are all shown.

 

But when I run the marco in my main workflow and loop through all the filters. It gives me only part of the records. See below:

macro2.PNG

 

Notice that some rows (record 2015, 2017, 2018) are dropped. 

 

Upon further investigation, alteryx automatically dropped the rows that has only "0"s. This is very strange because I still have my record name (i.e. "2015", "2017", "2018") that are not 0 and not empty.

 

Does anyone know how I can make the 0s rows shown as well?

 

Some information that might be useful:

- I put the output to file in the marco

- I output the data as excel workbook with 1 filtered in each tab (changing to other format does nothing different)

- I dropped "1ST" intentionally

- My Designer version is 2019.2

- All data are confidential so I cannot share my workflow or show more than the excerpt above. Any suggestion would be helpful.

 

Thanks!!!

Nebula
Nebula

Hi @billzhao 

 

What kind of macro is it, batch or iterative?  If it's a batch macro, can you share the configuration of the action tool and it's attached regular tool? Can you share images of the configurations of the specific tools that are supposed to change with each iteration?

 

How about a detailed explanation of what the macro is supposed to do?

 

Without some more information, the best we can say is that it's configured incorrectly and not being able to help you makes us grumpy! 

 

Dan

 

 

Meteoroid

Hi Dan @danilang ,

 

Thanks for the comment and I really appreciate it. Let me try my best to explain this as detailed as possible.

 

Some background of what I am trying to do. My workflow turns a list of insurance claims into something called "loss triangles". So basically, I start with data like:

 

Claim numberCoverageAccident YearReport YearReport LagLoss Amount
C1A20152017220
C2A20162017130
C3A20162018240
C4A20182018050

 

and turn them into a triangular shaped table like 

 

 Lag 0Lag 1Lag 2Lag 3
2015002020
201603030+40 
201700  
201850   

 

Currently, when exporting to excel, my triangle would look like this (2017 got dropped):

 

 Lag 0Lag 1Lag 2Lag 3
2015002020
201603030+40 
201850   

 

But this is just data for one coverage. The data I am working with have a lot of different coverages. I thought looping through the list of coverage names on a filter would give me one triangle per coverage.

 

So, I built this:

 

macro3.PNG

 

Sorry that I have to black out some pieces here. But the idea is exactly the same as what I explained. [K-Type] in the control parameter is a list of coverage names (e.g. the "1ST" you see under the filter is a coverage). The macro input is my complete claim table.

 

When looking at the browse in the end of this macro, it give me a perfect triangle with no years being dropped for "1ST". So I thought, oh it works. But when running this in the main workflow it drops all the years with 0 claims. (Alteryx has its own mind and very smart...)

 

Hope this is enough information.

 

Thanks!!! 

Highlighted
Meteoroid

Some screenshots:

 

Macro setting:

macro5.PNG

Output setting

macro4.PNG

Nebula
Nebula

Hi @billzhao 

 

Excellent explanation.

 

For looking at your macro, and your sample data,  it looks like one of your Transpose or Cross Tab tools is using Accident year as the pivot.  Since 2017 doesn't exist in the Accident year, it doesn't show up as a column in your output

 

 

Can you expand on this by telling me the correlation between report lag and the lag columns

 

From this I think that the lag columns correspond to years before the max year, 2018.  so lag 3 is 2015, lag 2 is 2016, lag 1 is 2017.

 

For claims 1st row, the value 20.  Why is it repeated in lag 3 and lag 2.   in row 2, the value 30 is repeated 2 twice, but the Report lag is only 1.  Repeat this explanation for each of the claim rows.     

 

Claim numberCoverageAccident YearReport YearReport LagLoss Amount
C1A20152017220
C2A20162017130
C3A20162018240
C4A20182018050

 

 

 Lag 0Lag 1Lag 2Lag 3
2015002020
201603030+40 
201700  
201850   

 

 

Can you expand your sample data to include more than one coverage and provide the corresponding correct triangles?  Also, do your loss triangles always deal with the last 4 years or are they variable dependent on the number years in the claim file?

 

Dan 

Meteoroid

Thank you Dan! I'll look into the Transpose and Cross Tab tools as you suggested.

 

Sorry I missed a very important concept in my explanation.

 

As you said, report lag is the number of years between accident year and report year, and maximum lag is the accident year to current year (2018).

 

Claim numberCoverageAccident YearReport YearReport LagLoss Amount
C1A20152017220
C2A20162017130
C3A20162018240
C4A20182018050

 

20 and 30 are represented twice in the following table because this is called cumulative loss triangle. It tracks claims (losses) cumulatively in an accident year. For example, two losses happened in the accident year 2016, 30 is reported in 2017 (at lag 1), and 40 is reported in 2018 (at lag 2). So cumulatively, 30+40 is down under lag 2 for accident year 2016.

 

Another why to look at this is by looking at it diagonally. It means that [2018 & lag 0, 2017 at lag 1, 2016 at lag 2, 2015 at lag 3] all represent the cumulative losses reported in the calendar year 2018.

 

 Lag 0Lag 1Lag 2Lag 3
2015002020
201603030+40 
201700  
201850   

 

This process is necessary because losses do not get reported (/recognized) at the time when accident happen. As an example, when a car crashes in Dec 2016, $30 is reported for fixing the car. A month later in Jan 2017, the insured feels sick and realized that the accident give him an head injury a lot worse than he expected. So he claimed $40 on a medical bill. Some situation like permanent injury that caused immobility can claim Income replacement that will go on for decades (then we may see $20,000 reported every year).

 

Hope this hasn't board you completely. To answer you questions, my loss triangle has 35 years (35x35 matrix). and as an example, a bigger and more realistic sample data table (This should be converted to four 6x6 matrices):

 

Claim numberCoverageAccident YearReport YearReport LagLoss Amount
C1A20152017220
C2A20162017130
C3A20162018240
C4A20182018050
C5B20132018540
C6B20142015150
C7B20142018430
C8B20152015020
C9B20152016140
C10B20152016150
C11B20152016120
C12B20152018330
C13B20162017150
C14B20162017150
C15B20162018250
C16B20172017050
C17B20172018140
C18B20182018050
C19C20132013020
C20C20132013050
C21C20132014150
C22C20132017430
C23C20162017150
C24C20162018250
C25C20162018250
C26C20162018240
C27C20182018020
C28D20142015150
C29D20152016150
C30D20152016140
C31D20152018330
C32D20162017140
C33D20172018120
C34D20182018030
C35D20182018050

 

The corresponding triangles:

 

ALag 0Lag 1Lag 2Lag 3Lag 4Lag 5
2013000000
201400000 
2015002020  
201603070   
201700    
201850     

 

BLag 0Lag 1Lag 2Lag 3Lag 4Lag 5
20130000040
2014050505080 
201520130130160  
20160100150   
20175090    
201850     

 

CLag 0Lag 1Lag 2Lag 3Lag 4Lag 5
201370120120120150150
201400000 
20150000  
2016050190   
201700    
201820     

 

DLag 0Lag 1Lag 2Lag 3Lag 4Lag 5
2013000000
2014050505050 
201509090120  
201604040   
2017020    
201880     
Labels