Connect two tables and calculating a new value

[claeskarlsson]

Have three tables in my current QV script (attached in an excel-document):

Table 1: Company (Key) and Turnover (Value)

Tabel 2: PerfKey (Key), Position (Value) and Quantity (Value)

Table 3: Company (Key) and PerfKey (Key)

I want to calculate a new variable which is basically the Position (Value from table 2 with PerfKey as Key) divided with the Turnover (Value from table 1 with Company as Key). First of all I think I need to sum the Position by Company (since each company has multiple PerfKey, as seen in table 3) in order to generate a new table with Company and Position. I'm new to working in the QlikView script and have tried to search for an answer, but I can't seem to find any... 

[dplr-rn]

What do you intend to do with the variable? is that being used as a measure?

[claeskarlsson]

In order for me to use IntervalMatch for a forth table (which does not have a common key with the other tables) I need the calculated variable. The calculated variable will be used as a Key with the fourth table.

[dplr-rn]

Not very clear. Not sure how you want to a variable to hold value for each of Company (Key)
I would create a summarized column to table1 and use that

[claeskarlsson · Accepted solution]

Solved it. For those that are looking for the answer, here is how I solved it (if you have an idea on how it can be approved, please let me know).

PerfKeyCompanyMap:

mapping LOAD

   PerfKey,

   Company

FROM Excel.Table3...;

TempTable1:

LOAD

   applymap('PerfKeyCompanyMap', PerfKey, 'miss: ' & PerfKey) as Company,

   Position,

   Quantity,

   PerfKey

FROM Excel.Table2...;

TempTable2:

LOAD

   Company,

   sum(Position) as Position_T,

   sum(Quantity) as Quantity_T

Resident TempTable1

Group by Company;

STORE TempTable2

into QVD.Table_Sum...;

CompanyTurnoverMap:

mapping LOAD

   Company,

   sum(Turnover)

FROM Excel.Table1...

Group by Company;

TempTable3:

LOAD

   Company,

   Position_T,

   Quantity_T,

   applymap('ComapnyTurnoverMap', affvara, 0) as turnover_x

FROM QVD.Table_Sum...;

CalculatedTable:

LOAD

   Company,

   Quantity_T / turnover_x as TurnoverDays

Resident TempTable3;

From there on I can import another table with a lower and upper limit for TurnoverDays as well as the value i want to retrieve based on where TurnoverDays is relative to the lower and upper limit. To join the new table I can use Inner Join IntervalMatch.

 NewValueTable:

LOAD

   Lower.Limit,

   Upper.Limit,

   Value

FROM NewExcelLimitTable...;

Inner Join IntervalMatch (TurnoverDays)

LOAD

   Lower.Limit,

   Upper.Limit

Resident NewValueTable;

To clean up, I just dropped some tables and fields.

Drop table TempTable1;

Drop table TempTable2;

Drop table TempTable3;

Drop field Lower.Limit from NewValueTable;

Drop field Upper.Limit from NewValueTable;