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?
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)
(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?
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.
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.
