How to split one record into many using SubField?

[#1]

hi

I have sample date from a client, it comes in a very strange format, one line:

COR032532917ý17ý991701ý1706ý990225191ý31608ýMISC-TGT1ý1ý14171ý800ý03616ý0ý405IN14754NIM1NIMEMASCO8379ý8379ý8240083ýý

when pasted into word the records are split:

COR
032
5329
17ý17ý99
1701ý1706ý9902
25191ý31608ýMISC-TGT
1ý1ý1
4171ý800ý0
3616ý0ý405
IN
14754
NIM1
NIMEMA
SCO
8379ý8379ý8240

083
ýý

Lines 4-9 and 15 have 3 records per line, I want to load this into Qlikview and as a result have 3 lines. I'm using the SubField but I'm ending up with every possible variation - 407 lines... I attach my qvw, I would gladly appreciate some help

Thank you

Kasia

[demoustier]

Hi

I'm not sure to well undestand the expected result...

maybe attached file OK...

[rubenmarin · Accepted solution]

Hi Kasia,

That's how subfield works, if you want to retrieve only 3 record you can use the 3 parameter version in a bucle so each iteration gets one value from 4-9 and 15 fields.

I attach an example. I removed the qualify condition for this example.

[#4]

Thank you, but that's not quite what I want to achieve, your method outputs one line and I need three lines like this:

The records in red are those from the fields I use SubField on

Does it make sense now?

Thank you

[rbecher]

Hi Katarzyna,

if it's always 3 values in these fields you can use Load While:

LOAD

    @1,

    @2,

    @3,

    subfield(@4, 'ý', IterNo()) as @40,

    subfield(@5, 'ý', IterNo()) as @50,

    subfield(@6, 'ý', IterNo()) as @60,

    subfield(@7, 'ý', IterNo()) as @70,

    subfield(@8, 'ý', IterNo()) as @80,

    subfield(@9, 'ý', IterNo()) as @90,

    @10,

    @11,

    @12,

    @13,

    @14,

    subfield(@15, 'ý', IterNo()) as @150

    ,

    @16,

    @17

Resident Inline

While IterNo()<=3;

- Ralf

[#6]

Thanks Ruben, that's exactly what I want!

some records only have 2 records inside, is there a way around it without having to hardcode this part:

For i=1 to 3

?

[rbecher]

..or doing it dynamically using a field as indicator and count delimiters:

LOAD ...

While IterNo()<=(substringcount(@4, 'ý')+1);

- Ralf

[rbecher]

..I would not suggest to use a for loop on a large data set. Load While does exactly the same in an amazing speed!

[rubenmarin]

Check this attachment, ther is a previous step to chek the field with more records and use it in the iteration.

I also followed Ralf advise to use a while iteration.

[rubenmarin]

Seems that i didn't tested it well, if there are different records with different number of 'ý' characters there will not work well.

If you need, tell me and i'll modify accordingly.