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

Is there a scripting command to "merge" two tables?

Hi,

`

I am building an app over personell_data.

I have now reached a point where I have an idea, but to implement it, I lack some "vocabulary" in this scripting_language and I don't know whether doing this right away would keep the whole thing "slimmer":

- I have two base_loads now:

     - one from the masterdata_table where I have one record per employee

     - one from a second table where the employees are of course the same, but I have several records per emp.

=> I'm looking for a command (in the script) to sort of "shove the two tables together like two packs of cards", so that

     - say, my masterdata_table is table A and the second one is tale B

     => I want all records from table B where the employee_ID is XY concatenated to the one record from table A with ID XY,

           then the next employee_ID etc.

    => I know that process (a mixture between concatenating and joining the two tables) as the MERGE command from another  
         language <=> in QlikView, that command doesn't exist.

Has anyone come across this by chance?

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Why not just concatenate the two tables and then sort by appropriate fields (employee_ID, rowno (which could added to the original tables))?

View solution in original post

6 Replies
swuehl
MVP
MVP

Why not just concatenate the two tables and then sort by appropriate fields (employee_ID, rowno (which could added to the original tables))?

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Hi,

this sounds like you just want to do a join.

For example

Table 1

employee     field1

a                    1

b                    2

d                    3

Table 2

employee     field2

a                    4

b                    5

c                    6

Do you expect your end result to be something like this:

employee     field1     field2

a                    1               4

b                    2               5

c                                      6

d                    3

If so

LOAD * FROM Table 1

OUTER JOIN

LOAD * FROM Table 2

Or do you have shared value fields, in which case you want to use a CONCATENATE.

Marcus

sadickbasha
Partner - Contributor III
Partner - Contributor III

As long as the column names  are same in both the tables, Qlikview automatically merge the data and keep it with 1st table name. Even if the column names are not matching we can use the key work 'CONCATENATE' to merge 2 tables.

Not applicable

Hi,

Use the JOIN keyword, that you can precede with LEFT or RIGHT.

LEFT meaning that only the employees from table A will be merged. RIGHT means that only employees from table B will be in the future table.

Because you have several employee rows in table B, you will have to to a sum(...) GROUP BY employee to get an effective merge. sum() or any aggregation SQL function for all the fields you want t import from table B

Fabrice

christian77
Partner - Specialist
Partner - Specialist

Hi.

And INNER join will leave no nulls on both sides.

In QlikView the simple join is FULL OUTTER by default. That matches QlikView philosofy and associative model. There is always a way to take away null values.

The question is, How many fields in common have your tables. If there is only one common field Employee_id, do the join; If they are many common fields, do concat.


If records are from the same nature, concat.

datanibbler
Champion
Champion
Author

Hi,

I guess you all have it right somehow, swuehl was merely the first.

It is indeed so easy I didn't see it - of course, concatenating and then sorting will do the trick.

That will help keep the whole thing slim - I will have to build a subroutine and call that once for every employee, about 1k times, so if I don't have to do so much concatenating in the subroutine, that will keep the final execution_time low.

Thanks a lot!

Best regards,

DataNibbler