LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sgolding@mda-data.com

Hi Support,

I have a field that contains multiple values split by pipe ('|'). Without creating a custom python node or a filter node with N outputs followed by a Cat node, is it possible to output multiple instances of the original record based on a pipe split of this field? (example given below)

Also, I've looked into the do output brain script statment however I get the following error when I try to run the help example. (BRG Attached)
Error in line 2
A corresponding output statement must precede 'do output'

What I'm after example:

INPUT (3 Records)
--------------
department, employees
1,James|Mark
2,John|Steph|Tim
3,Kate

OUTPUT (6 Records)
---------------
department, employee
1,James
1,Mark
2,John
2,Steph
2,Tim
3,Kate

Kind regards,

Stephen.
Attachments:
do_output_err.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: MartyWalsh

Stephen,

you need to use: Core::Expand From List.

ListExpr: the field containing the list of names. eg Employees
Delimiter: "|"
ItemField: the name of the output field for each unique value in the list, eg "employee"

Cheers
Marty

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sgolding@mda-data.com

Thanks for the quick response Marty.

Much appreciated,

Stephen.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: abhiban1

I would like to know how does one split with delimiters horizontally, i.e., across columns.. Reverse concatenation basically.. Any way other than Output Delimited??

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

You can do this a few ways,l depending on your particular problem.

Do you have an example of some data you have, and what you would want it to look like? Cause one issue to solve is, what do you want to have those columns called? If you are splitting the data, you have many values now, that need to go into columns. Is there a constant number of columns? Does the names of the columns come from the data? Or are they known? Or do you just want to name them Column1, Column2, etc. Is there a maximum number of columns known, even if the actual amount fluctuates?

You can do it manually, using the split function, or you can do it with a bit of work using the Expand From List and maybe a Pivot Data To Names or an Agg node. You would need to provide a unique id before you Expand From List to make sure you can aggregate back to the same rows, and then the next step really depends a lot on the answers to the above questions.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: abhiban1

Help me out in these two cases please..

1) Number|ActivationDate|ServiceID|Balance (After having renamed Field name to this string)
111111|010112|AAABBB|1090
222222|040311|BBCCDE|2250

2)Servicestring (13digit)
0000000000111
0000111000001

The first one I obviously want to split into columns.. In the second I want each digit in one column, column names being say SS1, SS2, ..., SS13

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: abhiban1

Also another case here, perhaps not related, but often needed..

Number Recharges
11111 RC1
RC2
RC3
22222 RC2
RC3

I want this as..

11111 RC1
11111 RC2
11111 RC3
22222 RC2
22222 RC3

I have a feeling it's Expand from List that'll be used here, but can't figure how..

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Add a filter node, and use this bit of BrainScript:


if firstExec then prev_Number='Number'

if 'Number' == "" then Number=prev_Number else Number='Number' #see note 1

emit Number
emit Recharges

prev_Number = Number #see note 2


# the value of a COLUMN of data from the input will change with each new record processed,
# but VARIABLEs can carry persistent data from one record to the next
#
#note1: this line creates a variable that has the same name as an input column - they are two different objects
#note2: this line must happen AFTER the value is used above

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ltolleson

The attached graph has a solution for each of your problems. The trick for the first two is to use the filter nodes as I have shown to expand your data so that it can be used by the Pivot Data to Names node. You do this by using the "do output" command with the while statement. The third example is doing what Stony just explained. You are trying to fill empty columns with a value from a previous row. To do this you need to save the value from the previous record in a variable so that it can be used by the next record. You do this by setting the variable AFTER the emit statement.

Hope these help.

Larry
Attachments:
ExpandColumns.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: abhiban1

Thanks Larry and Stony.. Really appreciate your solution which was precise and clear..
Hope for such solutions and help in my future queries

Regards,
Abhinav