topic Advice on loading fixed width multi record structures in QlikView

Advice on loading fixed width multi record structures

Mon, 07 Apr 2014 15:59:37 GMT

I have a record set which comprises of several header and detail records, all structured differently with a fixed width schema ARRGHHH!.

Example:

H01 INV12345 90012 11/07/2013

H02 ABC Ltd

D01 PO12345

D02 END CAP 00001

D03 VAL0000010

Schema H01:

1 - 3 = Record ID

5 - 16 = Invoice Number

etc....

The individual record types (H01 & D02 etc) all have their own unique schema but I need all of the information to appear together in my target tables if it's to be of any use. I have tried importing using the fixed width wizards etc and whilst I can get the data in, it's not usable as there is no key to link the records together. Do I need an extension to be able to load this data?

Many thanks for your help

Natalie

Re: Advice on loading fixed width multi record structures

maxgro — Mon, 07 Apr 2014 18:55:56 GMT

File:

load * inline

[

String

H01 INV12345 90012 11/07/2013

H02 ABC Ltd

D01 PO12345

D02 END CAP 00001

D03 VAL0000010

];

Table:

load

subfield(String, ' ' , 2) as Field1,

subfield(String, ' ' , 3) as Field2,

subfield(String, ' ' , 4) as Field3

resident File

where left(String,3)='H01';

concatenate (Table) load

subfield(String, ' ' , 2) as Field2,

subfield(String, ' ' , 3) as Field4

resident File

where left(String,3)='H02';

// concatenate .......

drop table File;

Re: Advice on loading fixed width multi record structures

whiteline — Mon, 07 Apr 2014 18:58:36 GMT

Hi.

You can't do it automatically. Load every data as one field and parse every row using string functions.

If schemes are not supposed to change, it's easy to hardcode the logic.

Could you describe other schemes ?

Re: Advice on loading fixed width multi record structures

Tue, 08 Apr 2014 08:17:10 GMT

Hi,

The schemas will not change so I will follow the suggested approach. Many thanks for your help

Re: Advice on loading fixed width multi record structures

Tue, 08 Apr 2014 08:19:45 GMT

Thank you for providing the example - I will give this a whirl and let you know how it goes.

Gracie

Natalie