Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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;
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.
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;