Making multiple rows from specific value

[serendipitytech]

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):

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?

[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

[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 · Accepted solution]

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

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

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. 

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]

That can be done with some modification of the workflow and by using a generate row tools.  I may be able to look at it tonight or tomorrow morning.

[serendipitytech]

Well that totally worked! 

I used your idea and incorporated into the workflow I'm building for this (and hopefully other) vendor. I created a couple IF, ELSE IF statements to use. 

First, replacing the grade code the vendor uses:

IF [value] = "03-05" THEN "03,04,05"
ELSEIF [value] = "05-07" THEN "05,06,07"
ELSEIF [value] = "05-08" THEN "05,06,07,08"
ELSEIF [value] = "06-08" THEN "06,07,08"
ELSEIF [value] = "08-09" THEN "08,09"
ELSEIF [value] = "08-12" THEN "08,09,10,11,12"
ELSEIF [value] = "09-10" THEN "09,10"
ELSEIF [value] = "09-11" THEN "09,10,11"
ELSEIF [value] = "09-12" THEN "09,10,11,12"
ELSEIF [value] = "10-12" THEN "10,11,12"
ELSEIF [value] = "11-12" THEN "11,12"

ELSE [value] ENDIF

That covers all variations for this vendor - I'll have to add a check mechanism in as well to catch future alterations or additions. 

Then, Exploding on , then transposing the data, another IF ELSE IF

IF [value] = "00" THEN "1000002"
ELSEIF [value] = "01" THEN "1000003"
ELSEIF [value] = "02" THEN "1000004"
ELSEIF [value] = "03" THEN "1000005"
ELSEIF [value] = "04" THEN "1000006"
ELSEIF [value] = "05" THEN "1000007"
ELSEIF [value] = "06" THEN "1000008"
ELSEIF [value] = "07" THEN "1000009"
ELSEIF [value] = "08" THEN "1000010"
ELSEIF [value] = "09" THEN "1000011"
ELSEIF [value] = "10" THEN "1000012"
ELSEIF [value] = "11" THEN "1000013"
ELSEIF [value] = "12" THEN "1000014"
ELSE "no match"

The "no match" will be used as a summary tool to find any exceptions that need attention. 

Then on to the select and output, and I have my file for loading. 

I'd be interested in the generate row tool, I'll look at that here in a moment, but with your help, I've got one task done, now back to that little fire for a few minutes :)

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