topic Re: Tryin' to Understand Concatenate & Join in QlikView

Tryin' to Understand Concatenate & Join

Tue, 21 Jun 2011 21:57:59 GMT

Hey Everyone-

I have two (2) tables that really should be one (1) table. However, due to some brilliant data integrity, field names are different and I cannot figure out if I need to concatenate or join.

Table1:

ID, LastName

1, Smith

2, Johnson

3, Fogherty

Table2:

ID, Last_Name (notice the underscore for Last_Name)

4, Lord

5, Stronghold

I need to only have one table with only 2 fields: ID and LastName, but obviously from the contents of both Table1 and 2:

OUTPUT

Table3:

ID, LastName

1, Smith

2, Johnson

3, Fogherty

4, Lord

5, Stronghold

I know this is a newbie question. So much apologies, I just have been slammin' my head against the desk tryin' to figure this out!

Thanks!

Re: Tryin' to Understand Concatenate & Join

johnw — Tue, 21 Jun 2011 22:32:29 GMT

Post all the newbie questions you want in "New to QlikView". I think that's what it's there for.

Anyway, I believe you want concatenate, but you'll need to rename the last name from the second table to match the first table so that QlikView realizes they are the same thing. So...

Table3:
LOAD ID, LastName
RESIDENT Table1
;
CONCATENATE (Table3)
LOAD ID, Last_Name as LastName
RESIDENT Table2
;
DROP TABLES Table1, Table2;

In practice you probably won't actually need temporary tables 1 or 2. Just do the table 3 load directly from their sources to avoid the temporary tables.

Re: Tryin' to Understand Concatenate & Join

Tue, 21 Jun 2011 23:54:25 GMT

ACK! Thought I was posting on the newbie board. Apologies everyone.

John... thanks for your help. Really appreciate it.

Re: Tryin' to Understand Concatenate & Join

johnw — Wed, 22 Jun 2011 00:03:33 GMT

Heh, you did post on the newbie board. I was trying to say that you'd done the right thing and there was no reason to apologize for posting a newbie question to the newbie board, and you should feel free to post as many as you want. Unfortunately, what I said kind of came out sounding like the opposite! Sorry about that.

Tryin' to Understand Concatenate & Join

Mon, 27 Jun 2011 11:05:26 GMT

Hi John,

But where is the difference between Concatenate and Join?

I used at least only Join and Left Join, because I don't understand the difference.

Regards

vicky

Tryin' to Understand Concatenate & Join

Mon, 27 Jun 2011 16:48:00 GMT

I struggled with this at first as well and I've found this blog post to be very informative on the subject

http://www.qlikfix.com/2010/12/09/merging-tables-concatenation/

(Sorry community admins if posting links to other sites is against the rules.....I'll be happy to edit this post if necessary.)

To quote the most important line from that blog post:

........the CONCATENATE prefix lets you add rows to a previously loaded table, while the JOIN prefix lets you add columns to a previously loaded table.

Hope this helps!

Anosh

Re: Tryin' to Understand Concatenate & Join

johnw — Mon, 27 Jun 2011 16:51:56 GMT

Concatenate just appends the records from the new source onto the end of the existing table. There are many forms of join, but they all try to match up "key fields" between the two tables. In QlikView's case, they match on all fields of the same name. So let's take some examples. First, here's our basic script. The concatenate or join will go in the indicated spot:

Table:
LOAD * INLINE [
Customer, Sales
Andy, 123
Becky, 234
];
// either concatenate or a join here
LOAD * INLINE [
Customer, Country
Becky, Canada
Carla, Mexico
];

And here are the expected results for our various options. Hopefully I got all these right.

CONCATENATE (Table)

Customer, Sales, Country
Andy, 123, null
Becky, 234, null
Becky, null, Canada
Carla, null, Mexico

------------------------------

LEFT JOIN (Table)

Customer, Sales, Country
Andy, 123, null
Becky, 234, Canada

------------------------------

RIGHT JOIN (Table)

Customer, Sales, Country
Becky, 234, Canada
Carla, null, Mexico

------------------------------

OUTER JOIN (Table)

Customer, Sales, Country
Andy, 123, null
Becky, 234, Canada
Carla, null, Mexico

------------------------------

INNER JOIN (Table)

Customer, Sales, Country
Becky, 234, Canada

Re: Tryin' to Understand Concatenate & Join

johnw — Mon, 27 Jun 2011 16:59:22 GMT

awadia wrote:
To quote the most important line from that blog post:
........the CONCATENATE prefix lets you add rows to a previously loaded table, while the JOIN prefix lets you add columns to a previously loaded table.

That's probably a good rule of thumb to keep in mind, but as the inner and outer join examples show, it's not quite that simple. You can use inner join to add columns and/or remove rows. You can use an outer join to add columns and/or rows. For instance, this would ONLY add rows, not columns:

Table:
LOAD * INLINE [
Customer, Sales
Andy, 123
Becky, 234
];
OUTER JOIN (Table)
LOAD * INLINE [
Customer, Sales
Becky, 234
Carla, 345
];

Customer, Sales
Andy, 123
Becky, 234
Carla, 345

In this example, outer join is used to eliminate exact duplicate rows between the two sources.

Re: Tryin' to Understand Concatenate & Join

johnw — Mon, 27 Jun 2011 17:13:27 GMT

awadia wrote:
Sorry community admins if posting links to other sites is against the rules.....I'll be happy to edit this post if necessary.

I'm not an admin and I've honestly never read the terms of use for the forum, but we post links to other sites all the time and I've never heard any grief. I THINK that what QlikTech cares about is that people get good information, period. The forum isn't a sales site where taking people to other sites would reduce sales. If QlikTech believes in their product, and I'm convinced they do, then correct information, regardless of source, can only help them overall.

Not that ALL information about QlikView is directly good for sales, such as if we're discussing weaknesses of the product or why some other product might serve a particular person's needs better than QlikView. But if the product overall is good, this sort of honest exchange is, in my opinion, good for QlikTech overall. I've never seen any censoring even when we're discussing advantages of competitor products. QlikTech seem like good people. I'm sure there must be some negative interactions out there, but I've had nothing but positive interactions with them.

Tryin' to Understand Concatenate & Join

Tue, 28 Jun 2011 05:08:46 GMT

Thanks John very much. Now it is easier for me to combine tables in the right way.

Have a nice day.

vicky