String_split in BAQ

Epicor ERP 10
,
4

To add on to @jdtrent’s comment…

Make a sub that returns the top 100 rows(or whatever the max number of fields you expect) of a table that has at least that many rows (parttran always has lots of rows). It only needs 2 fields:

  • Company (just something to link it to the next subquery)
  • Calc field:
    • Name: RowNum
    • Type integer
    • Expression: ROW_NUMBER() OVER(ORDER BY PartTran.TranNum)

Now make a subquery with the table containing the field to split , and the above subquery.

  • The Calc field will be:
    • Type nvchar,
    • Expression: [Ice].entry(ListGen.Calculated_RowNum,UD05_1.ShortChar01,',')

You’ll need to do something special to include “empty” sub strings (see the example below doesn’t include the field consisting of just the space)
This yields:

image

edit

It will give all 100 rows (or however many your Top xxx was in that first subquery). Use a subquery-criteria to filter out the blank rows.

2 Likes