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

How to split one record into many using SubField?

Wed, 24 Sep 2014 09:36:00 GMT

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

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

demoustier — Wed, 24 Sep 2014 09:52:54 GMT

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

maybe attached file OK...

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

rubenmarin — Wed, 24 Sep 2014 09:56:24 GMT

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.

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

Wed, 24 Sep 2014 10:03:45 GMT

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

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

rbecher — Wed, 24 Sep 2014 10:04:52 GMT

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

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

Wed, 24 Sep 2014 10:08:32 GMT

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

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

rbecher — Wed, 24 Sep 2014 10:09:06 GMT

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

LOAD ...

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

- Ralf

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

rbecher — Wed, 24 Sep 2014 10:22:06 GMT

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

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

rubenmarin — Wed, 24 Sep 2014 10:30:37 GMT

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.

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

rubenmarin — Wed, 24 Sep 2014 10:35:32 GMT

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.

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

Wed, 24 Sep 2014 10:40:45 GMT

For now we just need to let the client know that we can work with their data and it looks like we can, thank you for your first solution! The last records - 17, does have 'ý', but we don't know what this data mean so it's hard to say at this stage.. Thank you for your help!