Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

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
Champion
Champion
Author

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

View solution in original post

24 Replies
marcus_sommer

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
Specialist II
Specialist II

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
Champion
Champion
Author

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.

marcus_sommer

Hi DataNibbler,

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

- Marcus

datanibbler
Champion
Champion
Author

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?

marcus_sommer

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
Champion
Champion
Author

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.

marcus_sommer

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
Champion
Champion
Author

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?