topic How to split a string like subfield in QlikView

How to split a string like subfield

marcel_olmo — Mon, 16 Sep 2013 19:24:37 GMT

Hey guys!

I have a table like this :

Main Field	Secondary Field
1	qwertyuiopasdfghjklñzxcvbnm
2	mnbvcxzñlkjhgfdsapoiuytrewqopsasasasa

And I want to split the secondary field in parts of 7 chars.

I thought to use "subfield", but I need a char to reference the split, and I don't know how to insert a "diferential" chart every seven chars.

My result would be :

Main Field	Third Field
1	qwertyu
1	iopasdf
1	ghjklñz

How could I do this?

Many thanks in advance.

Regards

Re: How to split a string like subfield

Clever_Anjos — Mon, 16 Sep 2013 19:33:00 GMT

This code below does what you want

Table:

LOAD * INLINE [

F1, F2

1, qwertyuiopasdfghjklñzxcvbnm

2, mnbvcxzñlkjhgfdsapoiuytrewqopsasasasa

];

LOAD

F1 as [Main Field],

F2 as [Secondary Field],

IterNo() as i,

Mid(F2,(IterNo()-1) * 7+1, 7) as [Third Field]

Resident Table

While IterNo() <= div(len(F2),7)+1;

Re: How to split a string like subfield

MayilVahanan — Mon, 16 Sep 2013 20:16:14 GMT

Try like this also,

Table1:

Load * Inline

[

MF,SF

1,qwertyuiopasdfghjklñzxcvbnm

2, mnbvcxzñlkjhgfdsapoiuytrewqopsasasasa

];

Let vLen =0;

For j= 0 to NoOfRows('Table1')-1

vLen = 0;

Let vField1= Peek('SF',$(j),'Table1');

Let vField2 = Peek('MF',$(j),'Table1');

For i=0 to len(vField1)

if mod($(i),7) = 0 then

Load Mid('$(vField1)', $(vLen)*7+1, 7) as SF1, $(vField2 ) as MF AutoGenerate 1;

vLen = $(vLen)+1;

End if

But cleveranjos is an optimized way. This is also one way

Re: How to split a string like subfield

Anonymous — Mon, 16 Sep 2013 23:49:46 GMT

Clever Anjos , Mayil Vahanan Ramasamy,

Your both solutions are fine. I'm just adding one more

Source:
LOAD * INLINE [
Main, Second
1, qwertyuiopasdfghjklñzxcvbnm
2, mnbvcxzñlkjhgfdsapoiuytrewqopsasasasa ];

FOR n=0 TO 100 // the "limit" is hardcoded here, but can be pre-calculated in a variable
Result:
LOAD
Main,
mid(Second, $(n)*7+1, 7) as Third
RESIDENT Source
WHERE len(mid(Second, $(n)*7+1, 7))>0;
NEXT

Regards,

Michael

Re: How to split a string like subfield

marcel_olmo — Fri, 20 Sep 2013 11:12:11 GMT

Thanks Guys! It works like a charm! The 3 solutions did what I need.

Regards, Marcel.