Link table for accounting data in OLE DB data and budget data from Excel File

[Martin_G]

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!

[marcus_sommer · Accepted solution]

You don't need a link-table and no join-approaches else just concatenating the tables because they contain the same data. The transactions and the budgets are not different data - it's the same just the point of view is different. And by adding an extra Source field with values like: 'transaction' respectively 'budget' you could differentiate between them in dimensions and/or selections and/or set analysis.

We use it in this way and it worked very well whereby we take the first date of a month and not the last one which you may easily adjust with something like:

date(floor(monthstart(DateField)))

[Martin_G]

Hi Marcus, and thanks for your answer. I've tried it with data for one company and it seems to be working fine.

Do you have any input regarding the grouping on account intervals?

I was thinking of using if statements in the script like below to get grouping for use in a straight table or pivot table.

if(kto>=30000 and kto<37400,'Revenue',if(kto>=37400 and kto<40000,'other revenue', and so on. is there a better way to group ranges from a column 

Thanks again!

[marcus_sommer]

Normally there should be matching-scheme which accounts belong to which sub/main-groups within ERP system. If any possible try to use it to save time and to avoid an erroneous manually matching.

If it's not available you could use mapping-approaches instead of nested if-loops. Thinkable are nested mappings with an appropriate hierarchy, for example by using multiple overlapping values, like:

m: mapping load * [F1, F2
3, ...
300, ...
38456, ...
];

because a mapping worked like a VLOOKUP in Excel and takes always the first matching which means per the sorting of the mapping and/or the order of the calling it would be possible to implement even complex requirements.

Logically simpler would be to resolve the numeric ranges per internal while-loop into dedicated numbers and query them per mapping, like:

m: mapping load From + iterno() - 1, Return [From, To, Return
30000, 37399, ...
37400, 39999, ...
] while From + iterno() - 1 <= To;

and then you may call it per: applymap('m', Account, 'no match') as Group.

Beside this it might be more sensible not to push the grouping-information into the facts else to use such loop-resolving to create a dimension-table - especially if there isn't just a single grouping else multiple hierarchie-level.

[Martin_G]

Hi and thanks again Marcus for the help!

Unfortunately there is no such scheme available to get through the data base as you can build your own.

When using a mapping approach and I want hierarchies like example table below, can I use one table or do I have to add 5 different tables for each dimension when using mapping load and apply mapping?

For the second example with iterno() - would it look something like below in the scripts for KPIs in the example table?

m:
mapping load From + iterno() - 1, Return [From, To, Return

30000, 49999, Gross profit
30000, 59999, EBITDA
30000, 69999, EBIT
] while From + iterno() - 1 <= To;

Example of classifications

Many thanks again Marcus, it is very helpful!

[marcus_sommer]

You could use a single mapping by concatenating the multiple fields, like:

m: mapping load Lookup, F1 & '|' & F2 & '|' & F3 as Return from ...;

and then wrapping the applymap() like:

subfield(applymap(), '|', 1) as F1

to split the return-string again. I do this quite often and it worked very well.

Beside this I wouldn't use a mapping for your scenario else keeping them within a dimension-table which is also created with a while-loop to resolve the ranges to dedicated accounts (which could be later filtered against the accounts in the facts per where exists() or with a left keep statement) because a dimension-table makes more sense as adding n fields to the facts.

Further it's quite common in such scenarios to calculate partial sums which aren't originally covered by the dimensions but any dimension-value is needed to be able to plot any results. To get such things to work you could use a logic like: The As-Of Table - Qlik Community - 1466130.

[Martin_G]

Can you develop the dimension table concept with the while-loop with an example script? Is it the same as the iterno() mentioned before?

[marcus_sommer]

It's exact the same like in the above example - just removing the mapping-prefix from the load which makes the table visible + accessible again and loading as much fields as you need.

Just a hint - you may repeat the approach multiple times if there are several companies/countries within a holding with different P&L requirements and/or to consider any changes over the years - you need only to concat the account + company + year information within an extra KEY and concatenating all parts together.