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

[christian77]

I'd do

for i = 0 to 11

load

     $(i)+1                         as Month,

   subfiled(....,';',$(i))           as Budget

from ..........

next i

[tresesco]

I would do it like:

Load

SubField(Field,';') as Budget,

Month(MakeDate(1,RowNo())) as Month;

Load * Inline [

Field

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

[MK_QSL · Accepted solution]

Temp:

Load

  SubField(Field,';') as Budget,

  IF(MOD((RowNo() + 3),12)=0,12,MOD((RowNo() + 3),12)) as NO;

Load * Inline

[

  Field

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

];

Join

Load

  NO,

  SubField('$(MonthNames)',';',NO) as Month

Resident Temp;

Drop Field NO;

[manideep78]

Hi try like this

Temp:

LOAD * Inline [

String

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

];

Budget:

LOAD

Month(MonthName(MakeDate(1990,RowNo()),3)) as Month,

SubField(String,';') as Budget

Resident Temp;

DROP Table Temp;

PFA

Regards,
Manideep

[Karl_Hart]

Obviously the data I provided was just an example, and it works fine. When I try to aplly it to the real data it just hangs. Can you see where I'm going wrong? Thanks

Temp:

Load

  SubField(periodbal,';') as Budget,

  IF(MOD((RowNo() + 3),12)=0,12,MOD((RowNo() + 3),12)) as NO;

Load

  periodbal

;

SQL SELECT *

FROM PUB."oa_pcbalance";

Join

Load

  NO,

  SubField('$(MonthNames)',';',NO) as Month

Resident Temp;

Drop Field NO;

[MK_QSL]

Temp:

Load

     *,

  SubField(periodbal,';') as Budget,

  IF(MOD((RowNo() + 3),12)=0,12,MOD((RowNo() + 3),12)) as NO;

Load

     *,

  periodbal;

SQL SELECT *

FROM PUB."oa_pcbalance";

Join

Load

  NO,

  SubField('$(MonthNames)',';',NO) as Month

Resident Temp;

Drop Field NO

[Karl_Hart]

Thanks for the help

[MK_QSL]

If it's working, kindly close the thread by selecting appropriate answer...

[#10]

Have you tried a Crosstable? thats much more likely to work!