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.

[marcus_sommer]

Hi DataNibbler,

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

- Marcus

[datanibbler]

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?

[datanibbler]

Hi Marcus,

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

Never mind, here it is once more

[PradeepReddy]

I think we can achievie this using some ETL transformations.

Hope the thatched application carters your requirement.

[marcus_sommer]

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

[datanibbler]

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.

[marcus_sommer]

Hi DataNibbler,

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

- Marcus

[PradeepReddy]

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

[datanibbler]

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.

[datanibbler]

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