Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Relation and circle loops

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:

AuftragEbeneKostenartenVorj.Bud.VersionFYPeriode
80040111***Total GK0100B2012              1

Actual:

Nr ReferenzbelegKostenartKostenartenbezeichn.BelegdatumBuchungsdatumWert/BWährKonto GegenbuchungAuftragBelegkopftextBezeichnungGegenkontoartRefGeschäftsjahrBezeichnung des GegenkontosPeriodeFYBuchungskreisErfassungsdatumBenutzernameBelegnummerVersion
3003520440011REISEKOSTEN-HOTEL24.04.201328.06.201350,00911039980040111092355241168287RK April 2013K2012Diverse92012001412.06.2013USERNAME13128777A

Accounts:

KostenartHauptgruppeUntergruppe
440011GemeinkostenPersonalbezogene 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.

1 Reply
swuehl
MVP
MVP

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?