Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

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


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.

1 Solution

Accepted Solutions
datanibbler
Esteemed Contributor

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

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

24 Replies

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

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

pradeepreddy
Valued Contributor II

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

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

datanibbler
Esteemed Contributor

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

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

Hi DataNibbler,

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

- Marcus

datanibbler
Esteemed Contributor

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

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

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

datanibbler
Esteemed Contributor

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

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

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

datanibbler
Esteemed Contributor

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

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?

Community Browser