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

[Sergey_Shuklin]

Hello, Alexis!

You can rename the Price field in Table2 and then left join Table2 to Table1 by two fields (Date, Type). In resulted table use the condition if(Price2>0,Price2,Price1) as Price_res. You should get a table you need.

Note: Using Left Join is appropriate when Table1 is a some of cumulative table (consist of all possible dates needed for your report). If not - there are other ways to solve the task. But, hope this will help you!

[Sergey_Shuklin]

The second way is to create a new table with distinct values of fields Date and Type, then use Left Join from table1 to join Price as Price1 field, do the same for Table2, and then use the condition if(Price2>0,Price2,Price1) as Price_res.

It will be more correct than I offered earlier because in that way you don't lose the new dates!

[jonathandienst]

A full outer join and a resident load to get what you need:

[T_Desired Result]:

LOAD Date,

  Type,

  Price as T_Price1

FROM Table01;

// Full Outer Join

Join ([T_Desired Result])

LOAD Date,

  Type,

  Price as T_Price2

FROM Table02;

// Final table with latest Price

[Desired Result]:

LOAD Date,

  Type,

  Alt(T_Price2, T_Price1) as Price

Resident [T_Desired Result];

DROP Table [T_Desired Result];

[Anil_Babu_Samineni]

Don't take That much pressure and Complex model. You can simply use this

Table01:

LOAD Date,  Type, Price FROM [Data_2017_05_01.xlsx] (ooxml, embedded labels, table is Sheet1);

Outer Join // This is optional if you have same structure of 2 excels

Table02:

LOAD Date, Type, Price FROM [Data_2017_05_02.xlsx] (ooxml, embedded labels, table is Sheet1);

And then Create Straight table with fields use expression as Max(Price) meantime change it to Current Style from default to "Pyjama Green" and then Put 1 row for stripes from Style tab

I got output as Below

PS - Enclose Attached

[tamilarasu]

Hi Alexis,

Script:

Table02:  //New table
LOAD Date,
Type,
Price,
Date & Type as Key
FROM
[Data_2017_05_02.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate 

Table01:  //Old File
Load * Where Not Exists(Key);
LOAD Date,
Type,
Price,
Date & Type as Key
FROM
[Data_2017_05_01.xlsx]
(ooxml, embedded labels, table is Sheet1);

DROP Field Key;

Output:

[alexis]

Thanks for responding.

Your suggestion does not deal with the problem - look at your multiple values of 4th 6th and 8th of January.

Regards

Alexis

[alexis]

Hi Sergey,

Conceptually your idea is on the right track.

Like with other suggestions, this is not a 2-table problem but a multi-table problem - I have hundreds of input tables, one for each day.

Thanks for responding

[Anil_Babu_Samineni]

True, My apologies. I may not share updated one. So that i attached qvw

[alexis]

Thanks Jonathan

Your solution works (just as Tamil's below) and I like the use of "Alt" in your approach.

The "problem" that I have 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