Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to split one record into many using SubField?

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

1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

10 Replies
demoustier
Creator
Creator

Hi

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

maybe attached file OK...

rubenmarin

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.

Not applicable
Author

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

Capture3.JPG.jpg

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

Does it make sense now?

Thank you


rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

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
MVP
MVP

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

LOAD ...

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

- Ralf

Astrato.io Head of R&D
rbecher
MVP
MVP

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

Astrato.io Head of R&D
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.