1 Reply Latest reply: Jul 7, 2013 4:09 AM by Stefan Wühl RSS

    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.

        • Re: Problem with Relation and circle loops
          Stefan Wühl

          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?