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

[datanibbler]

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.

[datanibbler]

Hi,

there are still a few odd rows around.

That is because the original list is separated into blocks, with a few lines (a standard header and logo and such) inbetween.

For instance, line 54 in the original list has an item_no. => Line 61 has the BIN for that; The lines inbetween have neither (there is a break between blocks of data). So when I have two clauses

WHERE ISNUM([item_no] OR LEN([BIN]) > 1

=> then the lines inbetween should not be there.

I should be able to monitor that with Recno() which counts the lines in the source, disregarding WHERE clauses, no? So the lines should be there, but with neither item_no nor BIN.

In QlikView, however, it seems that line 54 has an item_no, but the BIN is in line 57 already (should be in 61). I have the impression that somehow those "inbetween_lines" are shortened.

Also, when I do the primary LOAD, I set the header to just 3 lines while in the original Excel_file, the header is 9 lines.

Strange thing all that ...

[datanibbler]

OK,

I think I made it now. I threw out another header line where there is some text in the column where I usually have the Bin_name and in a tablebox it now looks quite neat. I'll give it a try.

[marcus_sommer]

Hi DataNibbler,

maybe qv skiped empty records it there a X empty records one after the other. In the end you don't need them but of course it's easier to track your RecNo/RowNo with them. Maybe you could force them with an additionally field:

if(rowno() = 1, 1, peek('RowNoManually') + 1) as RowNoManually

or with

First XXX

Load ...

An alternatively to prepare and clean your data with the suggestions above you could try it with the table-wizard from qv. There are also many possibilities available to fill data, clean the garbage ... and if I remember correctly there is a preview for all / most of these actions.

- Marcus

[datanibbler]

Hi,

maybe you can help me: The last part - the actual VLOOKUP - I want to do in Excel, not in QlikView, so that the person who sent this to me will have their original file with just that information added.

Unfortunately I cannot use my favorite Excel_forum online, it has been blocked ...

It seems, however, that VLOOKUP will only work when I have a hard-coded (directly typed-in) lookup_value, not a formula.

I have the item_nr with a suffix in that table, however, so for the VLOOKUP to work I need to extract the first part - so I need a formula.

What to do there?

[datanibbler · Accepted solution]

Hi,

this is solved now.

For whatever reason, the VLOOKUP wouldn't work in Excel with a formula, only with a directly typed-in value - if, however, you multiply your formula with 1, that is registered as a numeric value 😉

Then it works.

I'll close this thread now.

Thank you very much for all your help!

Best regards,

DataNibbler