Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Add rows based on value in years column,

MattDuncan
7 - Meteor

Hi 

 

I have the following data set:

 

Transaction IDProduct IDValueDate PaidYears
T100001£487,694.0012/01/20171
T200002£19,909.0012/10/20170
T300003£250,656.0017/06/20161
T400004£144,386.0003/11/20170
T500005£430,619.0030/04/20171
T600006£147,934.0017/11/20170
T700007£359,626.0031/05/20171
T800008£353,061.0011/06/20170
T900009£148,527.0020/10/20170
T1000010£493,233.0018/08/20170
T1100011£171,198.0012/04/20171
T1200012£11,512.0019/11/20161

 

If in the years column there is a 1 i need to create a new row below it, copy the Product ID, add a %increase to the value and add 1 year to the date in that newly copied row.

 

Can anyone suggest the best way to do this. (there are 2500 rows - above is a small sample)

 

Thanks

3 REPLIES 3
Kenda
16 - Nebula
16 - Nebula

@MattDuncan See if the attached workflow accomplishes what you're looking for.

 

It is a little messy just because the monetary values have to be converted to numbers without symbols, and the Date Paid has to be converted to a date (not string) field. The main key, though, was to use the Formula tool to concatenate two values in one field (the original Value and the Value times 1.07). You can change that percentage to whatever you need. Then, I used a Text to Columns tool to split it into rows and a Multi Row Formula tool to add a year to the date. Lastly, you can add the symbols back in to the Value column and change the Date Paid column back to the format you were using before if need be.

 

Hope this helps!

cpituley
8 - Asteroid

Hi Matt Duncan, 

 

I understand your request is to have an additional row but would appended columns be sufficient? For instance, "Adjusted Year" and "Adjusted Value". 

 

If It is I would use the multi row formula tool.

 

Select Create New Field. Insert Name of field in this case "Adjusted Value" for the example.

 

Example formula: IF [years] == '1' THEN [Row+0:Value]*1.25 ELSE [Row+0:Value] ENDIF

 

This is saying if years is 1 return adjusted value otherwise return your original value. 

 

You could do the same for years. 

 

Let me know if this is sufficient for your needs. It stops you from having multiple entries for your transaction IDs/Product IDs. If I was working on a data set I would prefer single entries so this is entirely based on my preference and not your original request which I can't think of how to address off the top of my head. . . 

 

Kind regards,

Cameron

 

 

MattDuncan
7 - Meteor

Hi 

 

I ended up using the attached solution. Not the neatest but it worked for me

 

Appreciate people's input

Labels