topic CONCATENATE vs JOIN in QlikView

CONCATENATE vs JOIN

amien — Wed, 17 Nov 2010 09:31:34 GMT

What could be a reason to use concatenate? the result of a join looks much cleaner and memory efficient.

Do you prefer a join over een concatenate and why?

its clear that if database tables layout are the same that concatenate is preferered. so i'm more looking for reasons to use concatenate when tables are different and the result will look like this:

product | date | price | sold

shoes | 20100101 | 100 | -

shoes | 20100101 | - | 200

instead of

product | date | price | sold

shoes | 20100101 | 100 | 200

CONCATENATE vs JOIN

Miguel_Angel_Baeyens — Wed, 17 Nov 2010 09:50:01 GMT

Hi Amien,

In your example you talk about the same entity, in this case, products. But now think of getting a fact table built from different entities as invoices, shipment notes, delivery notes, stock and ledger entries, sales, purchases... each one with some common fields but some other required that are not shared. In this case, a join wouldn't make any sense, -there are no common fields or keys to join by-, and concatenation will work fine.

I haven't tested if concatenation performs better than join, since the ideal concatenation will require renaming of fields and create in each record the fields present in the other tables, but data is appended without taking care of already existing data (well, except for the fields to be created). A join requires at least one common field and reduction of data...

The result in both cases will be one table with all required data, which may be ok.

In your case, I would join rather than concatenate, but in many others, it will depend in the datamodel and the analyses required.

Hope this makes sense.

CONCATENATE vs JOIN

marcohadiyanto — Wed, 22 Dec 2010 05:12:46 GMT

hi,

i think this link will help you

http://qlikviewnotes.blogspot.com/2009/11/understanding-join-and-concatenate.html

it'll make you clear.

CONCATENATE vs JOIN

sunil2288 — Wed, 22 Dec 2010 07:29:35 GMT

Hi Amien,

Both concatenate and join has different usabilty...

Concatenation :-

When u r Concatenating one table with another . Then the resultant table will have data of 1st table along with the added data of second table.

Like Suppose there is two table tab1 and tab2 having two fields a,b in common. If u do concatenate to these two tables then the resultant table has each & every data of first and second table with the same two filed name.

Join:-

There are different types of join.If u r going to compare join with concatenate then

Join between two tables result in the common values .

Like in the above scenario. Join of tab1 and tab2 results the common fieldvalueonce and not commonvalue is also once occured in the table.

Where as in concatenation the resultant field contains all the records although same value is repeated..

So looking in to this scenario join is better than concatenation when no of records and redundancy check come into picture.

Hope this will help you.

Thanks

Sunil.

CONCATENATE vs JOIN

sunil2288 — Wed, 22 Dec 2010 07:31:04 GMT

Here join is better preferred.