Splitting string of text

[Karl_Hart]

Hi all,

I've got a string of text that shows me the monthly budgets in my finance system. For example:-

10; 20; 30; 40; 50; 60; 70; 80; 90; 100; 110; 120

are the monthly budgets for one area.

I not only want to separate this data into the 12 separate fields but I also want to assign it to a particular month.

So, I want the result to be

Month          Budget

Apr               10

May              20

Jun               30

etc etc

I've tried doing

SubField(periodbal, ';',1) AS [Apr Budget],

    SubField(periodbal, ';',2) AS [May Budget],

    SubField(periodbal, ';',3) AS [Jun Budget],

    SubField(periodbal, ';',4) AS [Jul Budget],

    SubField(periodbal, ';',5) AS [Aug Budget],

    SubField(periodbal, ';',6) AS [Sep Budget],

but this gives me 12 separate subfields

Any ideas?

Thanks

[MarcoWedel]

Hi,

in case there is more than one row of budgets in your input table, another solution could be:

LOAD
RecNo() as ID,
SubField(text,';') as Budget,
Month(Date#(AutoNumber(RowNo(),RecNo()),'M')) as Month
Inline [
text
10; 20; 30; 40; 50; 60; 70; 80; 90; 100; 110; 120
11; 21; 31; 41; 51; 61; 71; 81; 91; 101; 111; 121
12; 22; 32; 42; 52; 62; 72; 82; 92; 102; 112; 122
13; 23; 33; 43; 53; 63; 73; 83; 93; 103; 113; 123
14; 24; 34; 44; 54; 64; 74; 84; 94; 104; 114; 124
]

Creating the month field with "real" month values, not just some string pretending to be one, then ordering by and calculating with this field requires no additional effort:

hope this helps

regards

Marco