Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

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

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

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

6 Replies
MVP
MVP

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

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
Valued Contributor III

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

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
New Contributor III

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

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

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

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
Valued Contributor

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

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
Esteemed Contributor

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

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

Community Browser