Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How can I join 2 databases in qlik sense?

Hello Everyone! I need your help. I need to calculated the margin of each operation. The problem is that the operation table with the fields product id, order id, date, sales amount,and quantity  is located in a database but the production cost is in another database where I have the fields product id, date and unitary cost.

How can I make the data model to obtain the commercial margin of each operation? I am using the new data manager of qlik sense desktop 2.3

Thank you very much!

Tags (1)
10 Replies
oknotsen
Honored Contributor III

Re: How can I join 2 databases in qlik sense?

Connect to the first database, load the first table and call it OperationTMP.

Connect to the second database, load the second table and (left) join it with OperationTMP.

Next, the interesting part, do a resident load on OperationTMP, loading all fields and the quantity x cost field.

Drop the TMP table.

This is how the final part of the script would look:

Operation:

Load

     *,

     SalesAmount - Quantity * Cost as Margin

Resident OperationTMP

;

drop table OperationTMP;

May you live in interesting times!
reddys310
Honored Contributor II

Re: How can I join 2 databases in qlik sense?

Hi Ignacio,

Joining two databases should not be a problem at all in Qliksense.

Just extract the data using SQL into two separate tables, and name the fields which have to be linked , identical. Thanks it this will create an association between the two tables irrespective from which database it has been fetched from.

Re: How can I join 2 databases in qlik sense?

In qlik once you connect a new database , connection for the old data will get terminated automatically ...

i.e if you connect database A and then you connect data base B then A will get disconnected so

LOAD the data from the 1st database and then load the data from 2 data base build a common key which links both and then you can join the same.



connection Daata_base1;


table1:

LOAD KEY,

....

from table ;


connection Daata_base2;


table2:

LOAD KEY,

....

from table ;


now this 2 table s will automatically get linked , if you join the table than you could use a join condition in between but you need to have common key between them





Not applicable

Re: How can I join 2 databases in qlik sense?

Hello! Thank you for your help. I know how to join the tables, but I`m trying to calculate the margin, and this way it is not possible to have it. The cost is in one table but each operation is in the other table. I should join the field unitary cost to the main table

Not applicable

Re: How can I join 2 databases in qlik sense?

Hello! Thank you for your help. I know how to join the tables, but I`m trying to calculate the margin, and this way it is not possible to have it. The cost is in one table but each operation is in the other table. I should join the field unitary cost to the main table

Not applicable

Re: How can I join 2 databases in qlik sense?

Hello! Thank you very much for your help! How can I left join the tables? can you send me the script?

[Operation]:

LOAD

  [Date],

  [Client],

  [Comprobante] AS [Comprobante],

  [Quantity],

  [Net Sales],

  [product_id];

[Costs]:

LOAD

[Date],

  [product_id],

  [unitarycost]

FROM [lib://Documents/document.xlsx]

(ooxml, embedded labels, table is [Costs];

reddys310
Honored Contributor II

Re: How can I join 2 databases in qlik sense?

Hi Ignacio,

Which are the fields you would like to join in this case? Can you please post the whole script so that it will be easy to help solve the issue.

Thanks,

Sangram.

Not applicable

Re: How can I join 2 databases in qlik sense?

Hi! I need to add the measure :unitary cost x quantity, to the table Operation

[Operation]:

LOAD

  [Date],

  [Client],

  [Comprobante] AS [Comprobante],

  [Quantity],

  [Net Sales],

  [product_id];

[Costs]:

LOAD

[Date],

  [product_id],

  [unitarycost]

FROM [lib://Documents/document.xlsx]

(ooxml, embedded labels, table is [Costs];

Not applicable

Re: How can I join 2 databases in qlik sense?

Use

Table 1:

Load

......

From...;

Left Join(Table 1)

Load

......

From......;

Hope this helps