topic Re: Loading an Excel list (export from ?somewhere?) with 2-line records in QlikView

Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 09:20:21 GMT

Hi,

I have to do a simple vlookup between two tables in Excel.

Simple enough.

The remaining hurdle is: The one Excel_file (from where I have to fetch a value if there is any) has 2-line records - and

I need

- the item_nr. from the 1st line of every record

- the BIN nr. from the 2nd line of every record

=> So I cannot use a WHERE ISNUM to filter for just the lines with item_numbers for then all the BIN_numbers would not be connected.

Can anyone help me on this?

Thanks a lot!

Best regards,

DataNibbler

P.S.: I cannot use the logic that all odd-numbered lines have an item_nr. and the even-numbered have a BIN_number and then JOIN them because sometimes there are several lines for one item_nr., so that can vary.

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

marcus_sommer — Mon, 23 Feb 2015 09:46:34 GMT

Hi DataNibbler,

I would probably try to prepare the xls-load and split every record into two records and use the resulting table in a where-clause, maybe in this kind:

table:

Load

recno() as RecNo, rowno() as RowNo,

subfield(Field, chr(10) & chr(13), 1) as SplitField1,

subfield(Field, chr(10) & chr(13), 2) as SplitField2

From xyz;

Maybe you used no third parameter for subfield if you have an unknown number of nested records.

- Marcus

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

PradeepReddy — Mon, 23 Feb 2015 09:49:13 GMT

Is there any primary key field that exists on both rows of the same record?

For better understanding can u please share the sample data..?

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 09:55:31 GMT

Hi pradeep,

thanks a lot for your help! No, there is no key I could use to join the data to the item_number.

There is nothing. Really chaotic.

I guess I'll have to ask from where that list was exported and whether it can be exported in any other way. Maybe something was left out though I doubt it.

PFA a sample_file that looks like it - just the two columns that I need.

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

marcus_sommer — Mon, 23 Feb 2015 10:03:04 GMT

Hi DataNibbler,

a split or other preparing isn't possible? You will need something or you could forget it ...

- Marcus

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 10:13:58 GMT

Hi Marcus,

yes, I'm afraid you're right. I will ask if anything can be done about that format.

But maybe - all I'd have to do is actually populate the item_numbers downward - I could use PREVIOUS to just copy one number from one line to the next until I encounter another one? Could that work?

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

marcus_sommer — Mon, 23 Feb 2015 10:23:45 GMT

Hi DataNibbler,

if you could use subfield with a third parameter you will be have all information on one record. If not you could use previous or peek to bring the data in a correct order - maybe you could for this also use a combination from recno and rowno.

Maybe you also need some additional cleaning with keepchar(), replace and so on.

- Marcus

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 10:27:24 GMT

Hi Marcus,

what do you mean with Subfield? I can't figure out any way to do with that function?

I'm just experimenting with that PEEK() or PREVIOUS(), but that doesn't quite work yet.

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

marcus_sommer — Mon, 23 Feb 2015 10:32:16 GMT

Hi DataNibbler,

with subfield you could split a string into pieces with a defined delimiter. In a load-statement you could use a fixed version with a third parameter which will split only in these string-parts which a called and by the version without a third parameter it will create for each part a new record - which you could easily track with recno and rowno.

- Marcus

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 10:35:47 GMT

Hi Marcus,

yes, I know that use, you explained that to me a while ago and I used it successfully.

But I still cannot imagine how I could use it in this scenario?

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

marcus_sommer — Mon, 23 Feb 2015 10:39:49 GMT

Hi DataNibbler,

for this we will need a short data-example with perhaps two records and what is the relevant information from that.

- Marcus

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 10:41:15 GMT

Hi,

I'm still working on the populating downward.

One instance is

- In line 25, there is an item_nr.

- In lines 26 and 27, there is a BIN

- In line 28 comes the next item_nr.

The code >> IF(LEN(item)=0, PREVIOUS(item), item) <<

works fine from line 25 to 26 - I now have the same item_nr. in line 26 as in 25 - but it doesn't work on line 27.

Can you tell me why that is?

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 10:42:19 GMT

Hi Marcus,

I have attached such an example to one of my previous posts.

Never mind, here it is once more

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

PradeepReddy — Mon, 23 Feb 2015 10:55:50 GMT

I think we can achievie this using some ETL transformations.

Hope the thatched application carters your requirement.

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

marcus_sommer — Mon, 23 Feb 2015 10:57:10 GMT

Hi DataNibbler,

your approach is generally correct but some small detail is missing - have a look on the attachment. Maybe you need some more cleaning with where len(BIN) >= 1 or similar.

- Marcus

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 11:11:13 GMT

Hi Marcus,

it's still the same issue. It works well for one line - for instance, the item_nr from line 25 is populated into line 26 - but it is not populated further into line 27 (that also belongs to the item_nr. from line 25)

I'm at a loss here. I think I'm going to have a bite to eat now, maybe I can think of something lateron.

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

marcus_sommer — Mon, 23 Feb 2015 11:14:46 GMT

Hi DataNibbler,

I don't understand ... or are the real data different from the xls - in there it worked.

- Marcus

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

PradeepReddy — Mon, 23 Feb 2015 11:15:21 GMT

Hi DataNibble,

If you are looking for this type of data... Try with Fill Option from while loading the data. I have done the same thing in my application attached in previous post.

Thanks,

Pradeep

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 11:17:37 GMT

Ah, now I have my mistake (no, I haven't eaten yet, I'm not that fast 😉

It's like this

- There are two columns, A and B

- In column A, there is an item_nr. in, say, line 1, but not in 2,3,4

=> I can populate line 2 in column B based on line 1 in column A

<=> I cannot do the same for line 3 in column B because line 2 in column A is blank whereas line 2 in column B is
populated.

The difference is thus in the field_names. I tried renaming what is column B here up to now. Maybe that was the mistake.

Re: Loading an Excel list (export from ?somewhere?) with 2-line records

datanibbler — Mon, 23 Feb 2015 11:24:07 GMT

Hi,

yes, that is just what I need.

I think it works now - I have populated the item_nr downward so that now, every row has an item_nr., just now every row has a BIN.

Now I can just throw out those rows where there is no BIN and I'll finally have the table I need to do the vlookup that I need. Let's see ...