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

[datanibbler]

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

[swuehl · Accepted solution]

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]

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]

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.

[#5]

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]

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]

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