Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

10 Replies
oknotsen
Master III
Master III

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!
reddy-s
Master II
Master II

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.

avinashelite

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
Author

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
Author

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
Author

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];

reddy-s
Master II
Master II

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
Author

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
Author

Use

Table 1:

Load

......

From...;

Left Join(Table 1)

Load

......

From......;

Hope this helps