Stücklisten in QlikView - Recursive SQL

Heute würde ich mich gerne den Themen Stücklisten (oder bill of materials (BOM)) , QlikView und Recursive SQL-Statements widmen. Exemplarisch gehe ich hierbei von einer Stückliste für ein Auto aus:

Wie wir sehen können wird das Material "Schraube" in mehreren Bauteilen benötigt.  Insgesamt gibt es vier Subtrees die verschiedene Mengen an Schrauben benötigen. Um also insgesamt das Auto bauen zu können, benötigt man in unserem Beispiel 96 Schrauben.

Relational sind Stücklisten häufig in einer Parent-Child Struktur abgespeichert, da Stücklisten ja beliebig tiefe Verschachtelungen (Ebenen) haben können.


In QlikView wünscht man sich aber oft eine flache Darstellung mit einer benamten Spalte pro Ebene:


Naheliegend wäre es jetzt den QlikView Hierarchy Load an dieser Stelle einzusetzen. Dieser Befehl wurde in Qlikview 8.5 eingeführt, um zB. rekursive Vertriebshierarchien in die oben gezeigte Darstellung  zu bringen.

Führt man den Hierarchy-Befehl auf das gezeigte Datenset aus, liefert der Befehl  aber leider kein vollständiges Ergebnis. Die Knoten für die Subtrees:

  • Auto - Karosserie - Schraube
  • Auto - Karosserie - Schraube - Schraubenmutter
fehlen im Ergebnis. Entsprechend wäre in einer fertigen Applikation auch die Menge (Qty) für die Schrauben zu niedrig.

Warum ist das der Fall? QlikView's Hierarchy Befehl geht davon aus, dass jeder Knoten genau einen Vorgänger hat. Das klappt gut etwa bei einer Vertriebshierarchie: jeder Vertriebsmitarbeiter hat einen Teamleiter, jeder Teamleiter einen Gebietsleiter, usw. Bei Stücklisten ist der Fall schwieriger: Die Schraube beinhaltet zwar immer eine Schraubenmutter, die Schraube selbst wird aber an den unterschiedlichsten Stellen eingesetzt (Karosserie, Reifen, Zylinderdichtung und beim Auto selbst). Die Schraubenmutter kann auch ohne Schraube eingesetzt werden (im Beispiel direkt beim Auto, bei der Zündkerze).  QlikView scheint zwar noch einige Ebenen zu schaffen, aber irgendwann lässt es einfach Knoten weg. Leider ohne Fehlermeldung. Das Verhalten ist aber nach Nachfrage beim Support "Working as Designed".

Wie kann man nun die Stückliste trotzdem in QlikView einlesen? Der SQL Standard 1999 ist mittlerweile in vielen großen Datenbanksystemen implementiert, und bietet rekursive SQL Funktionen. Wenn man also das Auflösen der Stückliste auf die Datenbank auslagert, kann man die Daten in QlikView einlesen:

with rvaRec (ebene, path, Parent, Child, Quantity,QuantitySubTree,QuantitySubTreeValue) as (
 select 
  1 as ebene, 
  cast('|'+ Child as varchar(1000)) as path, 
  Parent,  
  Child, 
  Quantity, 
  cast(Quantity as varchar(1000)) as QuantitySubTree, 
  CAST(Quantity as float) as QuantitySubTreeValue 
 from [TestRVA].[dbo].[Stueckliste] where Parent is null
 union all
 (
 select 
  ebene+1 as ebene, 
  cast(path+'|'+c.Child as varchar(1000)) as path, 
  c.Parent as Parent, 
  c.Child as Child, 
  c.Quantity as Quantity, 
  cast(QuantitySubTree+'|'+cast(c.[Quantity] as varchar(50)) as varchar(1000)) as QuantitySubTree, 
  CAST(QuantitySubTreeValue*c.[Quantity] as float) as QuantitySubTreeValue  
 from rvaRec p, [TestRVA].[dbo].[Stueckliste] c
 WHERE p.Child=c.Parent
 )
)
select ebene, path, Parent,Child, Quantity,QuantitySubTreeValue,QuantitySubTree from rvaRec order by ebene,path;


Das Statement oberhalb liefert das Ergebnis für die Auto-Stückliste. Das Feld path zeigt durch das Trennzeichen '|' getrennt die flache Darstellung der Stückliste an. Stellt man das Ergebnis des QlikView Hierarchy-Load und des rekursiven SQL Statements gegenüber, so sieht man, dass das SQL-Statement die  zwei Subtrees:

  • Auto - Karosserie - Schraube
  • Auto - Karosserie - Schraube - Schraubenmutter
 korrekterweise im Ergebnis anzeigt.



Mit diesem Ergebnis kann nun in QlikView weitergearbeitet werden. Das Feld path ist zwar noch nicht optimal um es in Qlikview zu benutzen - für die Darstellung am Frontend soll das Feld path in die einzelnen Ebenen (== Spalten) zerteilt werden - aber da kann man sich mit QlikView Boardmitteln helfen.

Um ein Feld in QlikView zu zerteilen, gibt es den selten genutzten Load From_Field Befehl:

load
*
From_Field(Tablename,path)
(txt,utf8,explicit labels,delimiter is '|', msq);
Mit dem Parameter delimiter is '|' gibt man das Trennzeichen des bereits geladenen Feld path an. Für jede Ebene generiert QlikView nun ein Feld in der Syntax @Ebene (also @1, @2, @3, usw). Um zum endgültigen Ergebnis in QlikView zu kommen muss man also entsprechend die generierten Felder sinnvoll umbenennen und zu einer Tabelle zu joinen (ähnlich dem Generic-Load).

Die fertige Applikation sieht dann wiefolgt aus: die Felder Nodename1 bis Nodename7 entsprechen dem Feld path aufgesplittet nach dem Trennzeichen. Als korrekte Quantity für das Material "SCHRAUBE" wird in der Pivottabelle 96 angezeigt. Die Pivottabelle zeigt die Baumdarstellung mit ausmultiplizierten Mengen!


 Ein komplettes Skriptbeispiel, sowie ein Beispiel für die Limitation des Hierarchy-Loads finden Sie hier!



3 Response to "Stücklisten in QlikView - Recursive SQL"

  1. Anonym says:

    Hallo,

    ich stehe vor dem selben "Problem" mit dem Hirachy Befehl von QlikView.

    Könnten Sie den unteren Link nochmal aktualisieren?

    MfG

    Hallo !

    Downloadlink sollte jetzt wieder klappen!
    Alternativ gibt es auch folgendes Beispiel in der QlikCommunity, wie man es mit QV-Hausmitteln schaffen kann: http://community.qlik.com/docs/DOC-5497

    Es sieht so aus, als würde in QlikView 11.20SR12 nun der Hiearchy Load doch das korrekte Ergebnis für eine BoM liefern können. Das oben genannte Beispiel funktioniert nun korrekt.

    Die gesamte Diskussion dazu findet sich unter:
    https://community.qlik.com/blogs/qlikviewdesignblog/2015/08/31/bill-of-materials

Kommentar veröffentlichen

heldendaten GmbH,2017