Creating a connection between two tables

[bellesol]

Hi,

I have two tables, a Table of "Production output" and a table of "expenses".

the connection between the two tables is the column- Division.

The problem is that in the table "Production output" I have 3 different divisions (Division A+ Division B + Division C). and in the table "expenses" those 3 divisions are displayed as one division "Division A+B+C".

In order to display the expenses per division, I need to divide the expenses between the 3 divisions according to their output, for Example:

TABLE "Production output"

Division A - 50

Division B - 100

Division C - 50

Division  E - 100

***Total output of division A+B+C=200***

TABLE "expenses"

DIVISION A+B+C - 1000

DIVISION E - 300

what I want to display is, expenses by division:

Division A  - 250 ***(50/200)*1000***

Division B - 500*** (100/200)*1000***

Division C - 250 ***(50/200)*1000***

Division E - 300 

Is it possible to create a connection between those two tables in this way? so when users select division "A+B+C",

he will get the display as above?

and Is it possible to create this calculation in the script only?

Thanks in advance! 

[Taoufiq_Zarra · Accepted solution]

Hi,

I made a long script to make the logic clear, otherwise there's a lot of optimization you can do.

but the principle is that you always have "+" in the table TABLE "expenses" no for example A+B-D

the result obtained

attached the qlikview file as also

input1:

load * inline [
Division,production
Division A,50
Division B,100
Division C,50
Division E,100
];

input2:

load RowNo() as key,* inline[
Divisions,expenses
Division A+B+C, 1000
Division E, 300
];


temps1:
NoConcatenate
LOAD trim(PurgeChar(Division,'Division')) as Division,production resident input1;

temps2:
NoConcatenate
load trim(PurgeChar(Divisions,'Division')) as Divisions,expenses,key resident input2;

DROP table input1,input2;


temps3:
NoConcatenate
load trim(SubField(Divisions,'+')) as Division,expenses,key resident temps2;

DROP table temps2;

output:
NoConcatenate
load * Resident temps1;
left join 
load * Resident temps3;

DROP table temps1,temps3;

output2:
NoConcatenate

load 
key,
sum(production) as somme

resident output
group by key;
Right join
LOAD *
resident output;
drop table output;


Final:
NoConcatenate
load
Division,
(production/somme)*expenses as result
Resident output2;

DROP table output2;

[bellesol]

WOW! THANK YOU SO MUCH!

I couldn't ask for a better answer! 😍