Hi,
I have a problem where I need to get Transaction data from a OLE DB data source and then add en Excel file with manually entered Budget data for four different companies, the load data look like this compressed with the relevant fields for two of the companies as below.
I add the company name as Client field for every company and the joined data for Transactions table is working as supposed.
My problem is that the budget data is added on the last day of the month because it's mainly relevant to look at this aggregated per month. But since there are multiple transactions in the Transaction data the last day of the month every month the Budget data get multiplied for every occurrence of that date.
What I've found is that I should use a Link table to solve this issue but it seems that my skills is not enough to get the script right.
I would like to group interval of accounts for example revenue, material cost, gross profit etc. for set analysis and add a master calendar (or similar) to do analysis on different time periods. Is it possible to have a Link table for Client, account and date fields and then add grouping and master calendar connected in the Link table or do you have to do this in the Load for the original data sources?
LIB CONNECT TO 'Briljantdata - MGA (ad_ext_kons)';
Transactions:
LOAD
'MGA' as Client,
bel, // (amount field)
kto, // (Account field)
trdat; // (Date field)
SQL SELECT
bel,
kto,
trdat
FROM rtr;
LIB CONNECT TO 'Briljantdata KJ (ad_ext_kons)';
join(Transactions)
LOAD
'KJ' as Client,
bel, // (amount field)
kto, // (Account field)
trdat; // (Date field)
SQL SELECT
bel,
kto,
trdat
FROM rtr;
LOAD
'MGA' as Client,
kto, // (Account field)
trdat, // (Date field)
Budget_belopp // (Amount field)
FROM [lib://AttachedFiles/Qlik sense Budget MGA 22-23 & 23-24.xlsx]
(ooxml, embedded labels, table is Budgetdata_MGA);
LOAD
'KJ' as Client,
kto, // (Account field)
trdat, // (Date field)
Budget_belopp // (Amount field)
FROM [lib://AttachedFiles/Qlik sense Budget KJ 22-23 & 23-24.xlsx]
(ooxml, embedded labels, table is Budgetdata_KJ);
Help is much appreciated!
