Merging multiple tables

[alexis]

Hi

I have a requirement to read multiple Excel files (all of the same format but produced daily). The idea is that later files are stronger than older files and therefore any matches found, the value from the newer file should be used - the following screen shot should make it easier to understand.. The BLUE tables are my source files and the GREEN is what I want to achieve - instead, the closet I came to the desired result is the RED table where I used OUTER JOIN to join the tables.

I have attached a test application and the 2 excel files

Any help would be hugely appreciated.

Alexis

[alexis]

Thanks Tamil

Your solution works (just as Jonathan's above) and I like that you included a working QVW with your response.

The "problem" that I have, as I explained earlier, is that the solution needs to iterate through hundreds of data files (I just included just 2 data files to simplify the explanation). Can your solution be adapted to work iteratively?

Thanks again

Alexis

[alexis]

By the way I am not looking to keep the Max(Price) .

I am looking to keep the latest Price.

rgrds

Alexis

[jonathandienst]

>>The "problem" that I have is that the solution needs to iterate through hundreds of data files ...

It is certainly possible, but you would have to do it file by file which might be quite slow. It sounds to me like you will need an incremental approach (assuming the files are added daily or monthly). Then you could replace the "file1" load with a load from a qvd, replace the "file2" load with the most recent update and store the result table back into the QVD.

[alexis]

Hi Jonathan,

Your incremental approach with a Master.qvd and then the addition of a new file every day etc will ONLY work if the latest file is the ONLY file that may contain "corrections" to the Master. In other words what if they decide that a value from a file from last week has a correction that needs to be added? I have left that question with the users, because the incremental approach is the one that makes most sense.

Thanks again

Alexis

[tamilarasu]

Hi Alexis,

Jonathan suggested the best solution. Consider using incremental load concept in this case. Good luck.

[Sergey_Shuklin]

Hello, Alexis!

You can use a loop-load if you have a lot of tables.

Please, look at the picture and the attachement below.

The FileTime() function - is your mark for latest file in load. You use it to filter output result.

[#8]

Hi Alexis

Have u tried with this syntax

SQL Select * From Intload.xls Where Date_updated > $(vModDate)

[alexis]

I have multiple files Karthik so your suggestion will not apply in this case - thanks for responding

[alexis]

Thanks for the response and sample code.

It works for iterating through a number of files

It does not work for what I wanted to do in terms of which values should show in the final result.

Both Jonathan's and Tamil's suggestions offer the correct result.

Thanks