topic Re: Splitting string of text in QlikView

Splitting string of text

Karl_Hart — Fri, 12 Sep 2014 12:19:11 GMT

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

Re: Splitting string of text

christian77 — Fri, 12 Sep 2014 12:25:08 GMT

I'd do

for i = 0 to 11

load

$(i)+1 as Month,

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

from ..........

next i

Re: Splitting string of text

tresB — Fri, 12 Sep 2014 12:27:34 GMT

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 ]

Re: Splitting string of text

MK_QSL — Fri, 12 Sep 2014 12:45:54 GMT

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;

Re: Splitting string of text

manideep78 — Fri, 12 Sep 2014 13:04:14 GMT

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

Re: Splitting string of text

Karl_Hart — Fri, 12 Sep 2014 13:36:58 GMT

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;

Re: Splitting string of text

MK_QSL — Fri, 12 Sep 2014 13:48:23 GMT

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

Re: Splitting string of text

Karl_Hart — Fri, 12 Sep 2014 14:40:50 GMT

Thanks for the help

Re: Splitting string of text

MK_QSL — Fri, 12 Sep 2014 14:52:08 GMT

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

Re: Splitting string of text

Fri, 12 Sep 2014 16:12:54 GMT

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

Re: Splitting string of text

MarcoWedel — Fri, 12 Sep 2014 18:41:35 GMT

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