topic Re: Re: Separating records in QlikView

Separating records

datanibbler — Wed, 08 Oct 2014 09:43:04 GMT

Hi guys,

we have an Excel file that someone here on site draws from an Access-based tool that belongs to the customer - it is used by others and we can't influence what it looks like.

So here's the challenge (the first one, there are several, but this post is just for the first one):

=> The way it comes from the original tool, this list looks like sh...
(meaning: In one record, there can be one item_number - or several) - AND

following the item_nr, there CAN be a nr. of items (like "1 Stk"), but it has no known separator (not always the same) and it
does not always have the suffix "St" - there might be only a number.

=> I have to somehow separate that into one item_number per record, for there's no way someone can do that manually as often as we need it (every 30min) - a colleague now does it once a day, but the data can change instantly, so every 30min is the current target.

Can anybody help me there? Is there a function I can use to separate one record into several?

Thanks a lot!

Best regards,

DataNibbler

Re: Separating records

datanibbler — Wed, 08 Oct 2014 10:42:08 GMT

Hi,

OK, I'm one little step further now.

I can use the FROM_FIELD loading option to load the contents of that field only.

=> Now I have to find a way to separate what is to be one record and what is to be the next.

(that will be a bit difficult because that list doesn't have anything like a fixed delimiter)

=> The best I can think of is - the item_nr. has 7 digits. The amount (that can, but doesn't need to be there) usually has less - there are rare exceptions, but usually that's correct - so I can say, the one nr. in that field that's 7 digits long is an item_nr and thus constitutes one record - the rest up to the next 7-digit-long number goes into the same record.

Can anyone help me with the formatting? I have never done this - and I need to get somewhere fast with this (as always)

Thanks a lot!

Best regards,

DataNibbler

Re: Separating records

marcus_sommer — Wed, 08 Oct 2014 10:43:57 GMT

Hi DataNibbler,

I would try the following steps:

- removing from letter-signs

- replacing from possible delimiters

- subfield load

maybe so:

Load subfield(replace(replace(keepchar(ITEM, '0123456789,;+*-/\|'), ' ', ','), ';', ','), ',') as ITEM-New From xyz;

- Marcus

Re: Separating records

datanibbler — Wed, 08 Oct 2014 11:37:39 GMT

Hi Marcus,

yes, I will try something like that.

Thanks for helping!

(so I don't actually need that formatting option shown in the help_file?)

Best regards,

DataNibbler

Re: Separating records

datanibbler — Wed, 08 Oct 2014 11:46:20 GMT

Hi Marcus,

my code is like this:

LOAD
KeepChar(Mat, '0123456789') as Mat_Nrs
From_Field(SOFA_Liste, Mat)
;

(right now, that is an alias name I have given to the field using the AS option - QlikView returns an error "field not found" - when I use the original name, the error is "error upon opening file"

What am I doing wrong here?

Thanks a lot!

P.S.: OK, it works now - I just load everything from that using the * - that gives me three neat records, one for every item_nr, complete with everything else, I can split it in the aftermath.

<=> My issue is: QlikView defines the first thing it finds in that field as the field_header. How can I tell QlikView to use the actual field_header?

Re: Separating records

marcus_sommer — Wed, 08 Oct 2014 12:07:48 GMT

Hi DataNibbler,

I think to load "From_Field" has a rather theoretical advantage towards a normal load from a table and I have struggeled with them every time I have tried it and so I don't use it - therefore take simply a resident load on your table.

- Marcus

Re: Re: Separating records

datanibbler — Wed, 08 Oct 2014 12:14:30 GMT

Hi Marcus,

I will attach a small sample file so you understand what my issue is and why I cannot use a regular RESIDENT LOAD.

I need to use that FROM_FIELD. There's no other way (that I can see).

And I get good results - QlikView neatly splits the field_contents into three records - only it uses the first of those records as the header.

Re: Re: Separating records

datanibbler — Wed, 08 Oct 2014 12:24:36 GMT

Hi Marcus,

now I have it - so far. Now I have a field with three separate records - now I have the issue that the remainder of the first line (I have loaded only one line for testing that) needs to be loaded three times - for I have split one field into three records - just populate it downwards.

Can you tell me how I can do that?

Re: Re: Re: Separating records

marcus_sommer — Wed, 08 Oct 2014 13:11:20 GMT

Hi DataNibbler,

so far it's not very complicated - see attachment. If you have more various field-structures you need more from the preparing-steps.

- Marcus