Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Could you please advise as to what I might be doing wrong. I just despair!!
I created a file (dashboard) last month and all works fine, but after a bluescreen with open qlikview is it not possible to open it again.
Now i create this file again but now i have problems with realtions and circle loops but why.
I have 4 datatables (Actual / Budget / Forecast /
Benchmark planning)
Budget - Forecast - Benchmark planning with same structure
Budget:
LOAD Auftrag,
Ebene,
Kostenarten,
if(Kostenarten='Total Umsatz','Erlöse',
if(Kostenarten='Total Personal','Personal',
if(Kostenarten='Total CoS','CoS',
if(Kostenarten='Total GK','Gemeinkosten'))))as Kostengruppe,
Vorj.,
Bud. as BudgetAmount,
'B' as Version,
'2012' as FY,
'1' as Periode
FROM
D1A_Budget.xlsx
(ooxml, embedded labels, table is P01)
where Ebene ='***';
Actual
Master_Buchungsbeleg:
LOAD [Nr Referenzbeleg],
Kostenart,
Kostenartenbezeichn. as Kostenartenbezeichnung,
Belegdatum,
Buchungsdatum,
[Wert/BWähr] as Wert,
[Konto Gegenbuchung],
Auftrag,
Belegkopftext,
Bezeichnung,
Gegenkontoart,
RefGeschäftsjahr,
[Bezeichnung des Gegenkontos],
Periode,
Geschäftsjahr as FY,
Erfassungsdatum,
Benutzername,
Belegnummer,
'A' as Version
FROM
[Master_Buchungsbeleg_05_2013.xlsx]
(ooxml, embedded labels, table is Sheet1);
Now i want to compare Actual with Budget,Forecast and Benchmark.
Because in datatable Actual the information "Kostengruppe" is missing i create a table accounts
Accounts:
LOAD Kostenart,
Hauptgruppe as Kostengruppe,
Untergruppe
FROM
accounts.xlsx
(ooxml, embedded labels, table is accounts);
Example Data:
Budget:
Auftrag | Ebene | Kostenarten | Vorj. | Bud. | Version | FY | Periode |
80040111 | *** | Total GK | 0 | 100 | B | 2012 | 1 |
Actual:
Nr Referenzbeleg | Kostenart | Kostenartenbezeichn. | Belegdatum | Buchungsdatum | Wert/BWähr | Konto Gegenbuchung | Auftrag | Belegkopftext | Bezeichnung | Gegenkontoart | RefGeschäftsjahr | Bezeichnung des Gegenkontos | Periode | FY | Buchungskreis | Erfassungsdatum | Benutzername | Belegnummer | Version |
3003520 | 440011 | REISEKOSTEN-HOTEL | 24.04.2013 | 28.06.2013 | 50,00 | 9110399 | 80040111 | 092355241168287 | RK April 2013 | K | 2012 | Diverse | 9 | 2012 | 0014 | 12.06.2013 | USERNAME | 13128777 | A |
Accounts:
Kostenart | Hauptgruppe | Untergruppe |
440011 | Gemeinkosten | Personalbezogene Kosten |
Now it should be possible to compare Actual vs Budget vs Forecast vs Benchmark for every Kostengruppe.
It should work but it does not. Why ?
Thanks in advance for your help.
Budget and Actual are linked by multiple fields (e.g. by Auftrag, Version, FY).
Budget and Accounts are linked by Kostengruppe, Actual and Accounts by Kostenart.
This creates a circular loop.
You probably want to join the Accounts Table to the Actuals table instead.
LEFT JOIN (Actuals)
LOAD Kostenart,
Hauptgruppe as Kostengruppe,
Untergruppe
FROM
accounts.xlsx
(ooxml, embedded labels, table is accounts);
This should eliminate the circular loop, but still Budget and Actual are linked by multiple fields, creating a synthetic key and table.
Have you considered concatenating your fact tables?