Hi,
I have a table containing ProductName and I want to generate the OUTPUT column. The OUTPUT column a concatenation of ProductID with Serial#. How do I accurately parse the Serial# and create the OUTPUT column?
Serial # examples:
- 07-08_13 means Serial# 07, 08, 13
- 09-12 means Serial# 09, 10, 11, 12
- 01-03_05-08 means Serial# 01, 02, 03, 05, 06, 07, 08
- 01_04_05-08 means Serial# 01, 04, 05, 06, 07, 08
- 09_11_13_14 means Serial# 09,11,13,14
| ProductName | OUTPUT |
| AABBCC_07-08_13 | AABBCC-07 |
| AABBCC_07-08_13 | AABBCC-08 |
| AABBCC_07-08_13 | AABBCC-13 |
| AABBCC_09-12 | AABBCC-09 |
| AABBCC_09-12 | AABBCC-10 |
| AABBCC_09-12 | AABBCC-11 |
| AABBCC_09-12 | AABBCC-12 |
| EEFFGG_02 | EEFFGG-02 |
| HHIIJJ_01-07 | HHIIJJ-01 |
| HHIIJJ_01-07 | HHIIJJ-02 |
| HHIIJJ_01-07 | HHIIJJ-03 |
| HHIIJJ_01-07 | HHIIJJ-04 |
| HHIIJJ_01-07 | HHIIJJ-05 |
| HHIIJJ_01-07 | HHIIJJ-06 |
| HHIIJJ_01-07 | HHIIJJ-07 |
| KKLLLMM_01-03_05-08 | KKLLLMM-01 |
| KKLLLMM_01-03_05-08 | KKLLLMM-02 |
| KKLLLMM_01-03_05-08 | KKLLLMM-03 |
| KKLLLMM_01-03_05-08 | KKLLLMM-05 |
| KKLLLMM_01-03_05-08 | KKLLLMM-06 |
| KKLLLMM_01-03_05-08 | KKLLLMM-07 |
| KKLLLMM_01-03_05-08 | KKLLLMM-08 |
| NNOOPP_01_04_05-08 | NNOOPP-01 |
| NNOOPP_01_04_05-08 | NNOOPP-04 |
| NNOOPP_01_04_05-08 | NNOOPP-05 |
| NNOOPP_01_04_05-08 | NNOOPP-06 |
| NNOOPP_01_04_05-08 | NNOOPP-07 |
| NNOOPP_01_04_05-08 | NNOOPP-08 |
| QQRRSS_09_11_13_14 | QQRRSS_09 |
| QQRRSS_09_11_13_14 | QQRRSS_11 |
| QQRRSS_09_11_13_14 | QQRRSS_13 |
| QQRRSS_09_11_13_14 | QQRRSS_14 |
Accepted Solutions
It just had a minor error where a -1 was not removed when I restructured my code.
Names Default To Here( 1 );
dt = Current Data Table();
dt2 = New Table( "The Output",
New Column( "Product Name", character ),
New Column( "ProductID", Character ),
New Column( "OUTPUT", Character )
);
For( dtRow = 1, dtRow <= N Rows( dt ), dtRow++,
If( dtRow == 1 | dt:ProductName[dtRow - 1] != dt:ProductName[dtRow],
prodID = Word( 1, dt:ProductName[dtRow], "_" );
remainder = Substr( dt:ProductName[dtRow], Length( prodID ) + 2 );
While( Word( 1, remainder, "_" ) != "",
theWord = Word( 1, remainder, "_" );
If( Contains( theWord, "-" ),
numDigets = Length( Word( 1, theWord, "-" ) ) - 1;
zeros = Repeat( "0", numDigets );
For( loop = Num( Word( 1, theWord, "-" ) ),
loop <= Num( Word( 2, theWord, "-" ) ), loop++,
dt2 << Add rows( 1 );
theRow = N Rows( dt2 );
dt2:ProductName[theRow] = dt:ProductName[dtRow];
dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
dt2:output[theRow] = dt2:ProductID[theRow] || "-" ||
Substr( zeros, Length( Char( loop ) ) ) || Char( loop );
);
,
dt2 << Add Rows( 1 );
theRow = N Rows( dt2 );
dt2:output[theRow] = prodID || "-" || Word( 1, theWord, "-" );
dt2:ProductName[theRow] = dt:ProductName[dtRow];
dt2:ProductID[theRow] = Word( 1, dt:ProductName[dtRow], "_-" );
);
remainder = Substr( remainder, Length( theWord ) + 2 );
);
)
);Please make sure you take the time to study the code, so you completely understand how it is working.
Most likely you could write a formula with Lag to do that (or script with JSL), but you can also attempt to do that interactively but it takes quite a few steps:
1. Select ProductName column
2. Go to Cols/Utilities/Text To columns and use both - and _ as delimiters
3. Go to Tables and Stack and add everything else besides first column to Stack Columns selection
5. Remove Label column
6. Select both columns which are left and go to Rows / Row Selection / Select Dublicate Rows and delete those rows
7. Select one cell with empty value and choose Select Matching Cells and delete those rows
8. Now choose both colums and right click on the column header and create formula
9. Open the just created formula and change the , to _
10. Rename and delete columns/formulas
11. If you need to have ProductName there, you should be able to keep it there if you don't accidentally delete it in first step like i did
If you have to have - or _ depending on the serialnumber you could handle it in the concatenate formula most likely
Edit: I checked the question a bit more accurately and this is a bit more complicated than I showed here. You will have to add calculations for some of the serial numbers because they are between numbers. This solution won't work in those because some rows will be removed when duplicates are removed.
