Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
WOW! THANK YOU SO MUCH!
I couldn't ask for a better answer! 😍