Problem with Relation and circle loops

Sun, 07 Jul 2013 07:41:03 GMT

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

Buchungskreis

Erfassungsdatum

Benutzername

Belegnummer

Version

3003520

440011

REISEKOSTEN-HOTEL

24.04.2013

28.06.2013

50,00

9110399

80040111

092355241168287

RK April 2013

2012

Diverse

2012

0014

12.06.2013

USERNAME

13128777

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.

Re: Problem with Relation and circle loops

swuehl — Sun, 07 Jul 2013 08:09:56 GMT

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?

topic Re: Problem with Relation and circle loops in Water Cooler

Problem with Relation and circle loops

Re: Problem with Relation and circle loops