topic Re: Merging multiple tables in QlikView

Merging multiple tables

alexis — Fri, 12 May 2017 00:18:34 GMT

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

Re: Merging multiple tables

Sergey_Shuklin — Fri, 12 May 2017 04:29:07 GMT

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!

Re: Merging multiple tables

Sergey_Shuklin — Fri, 12 May 2017 04:37:09 GMT

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!

Re: Merging multiple tables

jonathandienst — Fri, 12 May 2017 04:48:37 GMT

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];

Re: Merging multiple tables

Anil_Babu_Samineni — Fri, 12 May 2017 04:57:25 GMT

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

Re: Merging multiple tables

tamilarasu — Fri, 12 May 2017 06:08:59 GMT

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:

Re: Merging multiple tables

alexis — Fri, 12 May 2017 06:32:17 GMT

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

Re: Merging multiple tables

alexis — Fri, 12 May 2017 06:34:13 GMT

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

Re: Merging multiple tables

Anil_Babu_Samineni — Fri, 12 May 2017 06:35:10 GMT

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

Re: Merging multiple tables

alexis — Fri, 12 May 2017 06:45:55 GMT

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

Re: Merging multiple tables

alexis — Fri, 12 May 2017 06:47:29 GMT

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

Re: Merging multiple tables

alexis — Fri, 12 May 2017 06:52:45 GMT

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

I am looking to keep the latest Price.

rgrds

Alexis

Re: Merging multiple tables

jonathandienst — Fri, 12 May 2017 09:51:52 GMT

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

Re: Merging multiple tables

alexis — Fri, 12 May 2017 10:04:31 GMT

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

Re: Merging multiple tables

tamilarasu — Fri, 12 May 2017 10:56:44 GMT

Hi Alexis,

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

Re: Merging multiple tables

Sergey_Shuklin — Sat, 13 May 2017 05:28:06 GMT

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.

Re: Merging multiple tables

Sat, 13 May 2017 11:07:56 GMT

Hi Alexis

Have u tried with this syntax

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

Re: Merging multiple tables

alexis — Sat, 13 May 2017 17:09:35 GMT

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

Re: Merging multiple tables

alexis — Sat, 13 May 2017 17:20:52 GMT

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