Making multiple rows from specific value

Hello everyone,

So - I'm not entirely sure how to ask this with proper terminology - but here goes:

I have a dataset of about 75k records, each record has a value from the list (gathered from the summarize tool):

Screen Shot 2017-04-04 at 3.46.21 PM.png

What I need to generate is a tab delimited file that contains the ID from the count distinct list above, and one record per digit of the groups above.

So, for example, all the 1532 items in the 11-12 group, the final file would need to have two entries for each ID with the corresponding new value, one for 11 and one for 12:

1234567 10005

1234567 10006

I have a list of values, but the list as one id for each value 00-12, and I need to end up with a list that as a one to one relation of item ID to value ID.

I was thinking of trying to use the Formula tool and create a complicated IF, Then, ElseIf, etc. But I wasn't sure if that was the best way, or if that would even end up generating a 1:1 list. Like if I put in

IF value = 11-12 THEN 10005 ELSEIF value = 11-12 THEN 1006 and so on?

Workflow

Transformation

Accepted answers

alex

Guessing on your methodology for assigning the new value or if an ID exists in more than 1 grade but these type of workflows could help. I created a data file that was not summarized yet, since this is where you need to assign IDs. Would need more info to provide a complete solution.

Results from lower path if ID exists in more than 1 grade category

grades.yxmd

All comments

MarqueeCrew

@serendipitytech,

My input data is similar to the picture that you provided, but not exact. You'll see that the input rows for 03-05 and 05-07 are multiplied as per your request. You can see the whole process inside of the version 11 workflow that is attached.

To configure the TAB delimited file, I use a CSV output file and change the delimiter from , to \t (\t = tab).

Cheers,

Mark

Making Multiple Rows.yxmd

alex

@serendipitytech- would your example for group 11-12 have 3,064 rows or 2?

serendipitytech

The result would be 3,064 rows. I shared the picture of the summary screen, as I had to use that to see what all values were used by this specific vendor that I had to match to our list. They are grade levels essentially, so 11-12 means that the object is intended for 11th and 12th grade, so for each of the 1532 records, I need to generate two records - one with an alignment to our 11th grade entry, and another with an alignment to our 12th grade entry.

I haven't had a chance to look at the work flow yet, as a little emergency came up this morning, which may lead to another posting here. I'm stumped with the results of a formula tool that match 100% when I run the results manually in the database, but I can't get the join tool to see the same match.

alex

Results from lower path if ID exists in more than 1 grade category

grades.yxmd

serendipitytech

I see the process there, I'm sorry for not including more information, I was trying to not confuse the matter with too much unnecessary information too.

The issue with this workflow is that it will work I think very well for 11-12, 10-11, etc, where there are only two grades involved, but in a case where it was 6-8, which would include 3 grades, I'd only get two columns, one for 6 and one for 8, and we would miss an entry for 7 it looks like?

So in this specific scenario, I can insert a Formula tool to change all of the used combinations to a comma separated list, then explode columns on the , and that should take care of these needs?

I'm excited about this approach, so I'm going to give this a shot here, this mornings "fire" has died down a little, but it has now presented it's own sets of issues. These forums are just so awesome, I can't thank you all enough.

serendipitytech

I see the process there, I'm sorry for not including more information, I was trying to not confuse the matter with too much unnecessary information too.

So in this specific scenario, I can insert a Formula tool to change all of the used combinations to a comma separated list, then explode columns on the , and that should take care of these needs?

I can also offer some more info on the grade aspect.

Basically, we have assessment items, each item can be assigned to multiple grades. This vendor provides grades in ranges, 11-12, 10-12, 6-8, etc. as well as individual grade levels.

Our database is setup to store grades associated to items in a 1:1 manner, so if an item with an id of 12345 is associated to grades 9-12 (9,10,11,& 12), it will have 4 records in the grade association table, each record corresponding to our unique ID number for that grade level. So, if the unique grade level IDs we had were 10005, 10006, 10007, 10008 we would end up with a list of:

12345 10005

12345 10006

12345 10007

12345 10008

alex

This is the generate rows version to solve the 9 thru 12 type products. I also added some more data rows to my example and renamed columns to clarify what the data is.

grade 5.JPG grade 6.JPG

grades2.yxmd

serendipitytech

That is a GREAT example of the tools involved, seeing the thought process through the workflow makes so much sense. And this has the benefit of being more future proof, as I believe it will handle any and all instances, so if the next set has a 0-3, and this set doesn't, then it will scale that up.

Since this is an ongoing task, I really can't thank you enough for providing such thorough help, and in the process I think I get a better understanding of some of the tools I haven't used yet. Month 4 with Alteryx, and it just keeps getting more useful!

Thanks again Alex!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703