Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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.
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
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
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
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];
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.
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];
Use
Table 1:
Load
......
From...;
Left Join(Table 1)
Load
......
From......;
Hope this helps