topic Creating a connection between two tables in QlikView

Creating a connection between two tables

bellesol — Sat, 16 Nov 2024 01:39:32 GMT

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!

Re: Creating a connection between two tables

Taoufiq_Zarra — Sat, 21 Dec 2019 17:09:07 GMT

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;

Re: Creating a connection between two tables

bellesol — Sun, 22 Dec 2019 11:00:53 GMT

WOW! THANK YOU SO MUCH!

I couldn't ask for a better answer! 😍