topic Re: Create link between two tables?.. in QlikView

Create link between two tables?..

Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

I am new to QlikView,

In QlikView generally we are saying two or more tables have same filed name automatically create link between two tables. I have difficulty to understand, how to create link between two tables, what type joins (using) are allowed inside into QlikView?

Regards,

Raja.

Re: Create link between two tables?..

maxgro — Tue, 24 Dec 2013 18:52:18 GMT

You can start from here, very very good doc

http://community.qlik.com/docs/DOC-3412

Re: Create link between two tables?..

VishalWaghole — Wed, 25 Dec 2013 08:07:19 GMT

Hi Raja,

PFA it will help you regarding joins.

- Regards,

Vishal Waghole

Re: Create link between two tables?..

Wed, 25 Dec 2013 12:21:52 GMT

Hi,

Thanks to all for valuable time for me. I am expecting a little different,

Please find this load script,

Product:
LOAD * Inline [
Product_ID,Year,Sales
101,2013,100
102,2013,150
103,2013,160
];

Customer:
LOAD * Inline [
Customer_ID,Product_ID,Purchase_DT
C_101,101,01-Jan-2013
C_102,102,01-Feb-2013
C_103,103,01-Mar-2013
];

QUALIFY Product_ID;
Collection_Details:
LOAD * Inline [
Customer_ID,Product_ID,Amt
C_101,101,1000
C_102,102,1100
c_103,103,1200
];

Now, three tables have same filed name automatically create link between above tables. In QlikView what type joins using?

Regards,

Raja.

Re: Create link between two tables?..

maxgro — Wed, 25 Dec 2013 22:04:53 GMT

This is an extract of the document Joins and Lookup, hope it helps on association in your data model

"...........

The data model defined in the QlikView script usually contains many tables that are linked with key

fields. The links are implicit joins that are not yet made. In other words, they define where joins

should be made when the user makes a selection. But since they are not yet evaluated, we prefer

not to call them “joins”. Instead, we call them “associations”.

When the associations are evaluated, they are evaluated as natural joins; i.e. QlikView requires the

key field to have the same value in the two tables. It is hence not possible to use any other

comparison, e.g. the following criterion cannot be used:

TableA.key >= TableB.Key

Further, the QlikView script can contain explicit joins. These are different and these we indeed call

“joins”. These are executed when the script runs and the resulting table will constitute one table in

the QlikView data model, unless it is dropped. See more about these in the next section.

Hence, the main difference between the associations and joins is that the associations are

evaluated at demand; as the user makes selections. As opposed to the joins that are evaluated

when the script runs.

A second difference is that a join is explicitly an inner join or an outer join (or a left or a right join)

whereas the nature of an association depends on the situation. The association can be evaluated

to a left join or a right join depending on where the user has made a selection. And with no

selection, the association is always evaluated to a full outer join.

For example, if you have a table containing customers and a table containing orders, and you

select some customers, then QlikView will make a left join: All the selected customers (left table)

will be possible, even though they are not represented in the order table. But only the orders (right

table) that are represented in the customer table are possible.

So when you make your data model in the QlikView script, you make the necessary data

transformations, sometimes using explicit joins, and you make sure to name the keys so that the

resulting tables are linked correctly. Also, you also make sure to name non-keys with unique

names so that these do not link.

......"

Re: Create link between two tables?..

Thu, 26 Dec 2013 04:43:25 GMT

hi, If u want you can use Inner Join, Left Join...etc like this.

Product:
LOAD * Inline [
Product_ID,Year,Sales
101,2013,100
102,2013,150
103,2013,160
];
Inner Join
Customer:
LOAD * Inline [
Customer_ID,Product_ID,Purchase_DT
C_101,101,01-Jan-2013
C_102,102,01-Feb-2013
C_103,103,01-Mar-2013
];

QUALIFY Product_ID;

Inner Join
Collection_Details:
LOAD * Inline [
Customer_ID,Product_ID,Amt
C_101,101,1000
C_102,102,1100
c_103,103,1200
];

Re: Create link between two tables?..

Thu, 26 Dec 2013 07:36:01 GMT

Hi,

Thanks you so much maxgro and dushan1987

Regards,

Raja.