Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: How to split one record into many using SubField?

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.

10 Replies
demoustier
Not applicable

Re: How to split one record into many using SubField?

Hi

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

maybe attached file OK...

rubenmarin
Not applicable

Re: How to split one record into many using SubField?

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

Re: How to split one record into many using SubField?

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
Not applicable

Re: How to split one record into many using SubField?

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

Not applicable

Re: How to split one record into many using SubField?

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
Not applicable

Re: How to split one record into many using SubField?

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

LOAD ...

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

- Ralf

rbecher
Not applicable

Re: How to split one record into many using SubField?

..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
Not applicable

Re: Re: How to split one record into many using SubField?

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
Not applicable

Re: Re: How to split one record into many using SubField?

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.