Data model with multi-stage flows

[andreas-ar]

Hello everyone,

I’m currently building a data model in Qlik Sense and would appreciate some guidance. I tried for hours but I can't find any solution. I think the best way would be a loop but I can't get the code right.

The structure looks like this:

• Purchases (EntryType = 0) are always the starting point.

• Sales (EntryType = 1) are always the end point.

• In between, there can be any number of stages consisting of Input (EntryType = 5) and Output (EntryType = 6).

• Links between stages are based on LotNo and FA_Nummer.

• One FA_Nummer can relate to multiple LotNo.

• Sales always represent the termination of the chain.

• There is no explicit field that directly connects a Production Reporting entry to the following Consumption entry.

I’d like to create a data model that allows me to trace a flow starting from Purchases, through any number of intermediate Consumption/Production steps, until the final Sale.

Because there’s no explicit link between a Production Reporting record and the subsequent Consumption record, I’m unsure how to correctly model these intermediate stages so the full chain can be followed.

Unfortunately there's no BOM in the source that I could refer to.

I attached an xlsx to outline how the data is connected.  

Has anyone faced a similar scenario, or do you have recommendations for how to structure this in Qlik Sense?

Thanks in advance!

[robert_mika]

Try maybe like that:

// Load all entries
Stages:
LOAD
LotNr,
OrderNr AS FA_Nummer,
'Purchase' AS Stage,
0 AS EntryType
FROM [ExcelFile.xlsx] (ooxml, embedded labels, table is Tabelle1);

CONCATENATE (Stages)
LOAD
LotNr,
OrderNr AS FA_Nummer,
'Input' AS Stage,
5 AS EntryType
RESIDENT StagesRaw;

CONCATENATE (Stages)
LOAD
LotNr,
OrderNr AS FA_Nummer,
'Output' AS Stage,
6 AS EntryType
RESIDENT StagesRaw;

CONCATENATE (Stages)
LOAD
LotNr,
OrderNr AS FA_Nummer,
'Sale' AS Stage,
1 AS EntryType
RESIDENT StagesRaw;

///////////////////////////

LET vMaxIterations = 10;

FOR i = 1 TO $(vMaxIterations)

ChainStep:
LEFT JOIN (Stages)
LOAD
Output.LotNr AS FromLot,
Output.FA_Nummer AS FA_Link,
Input.LotNr AS ToLot,
Input.FA_Nummer AS FA_Nummer
RESIDENT Stages AS Output
JOIN
RESIDENT Stages AS Input
WHERE Output.EntryType = 6 AND Input.EntryType = 5
AND Output.FA_Nummer = Input.FA_Nummer;

 CONCATENATE(ChainProgress)
LOAD * RESIDENT ChainStep;

DROP TABLE ChainStep;

NEXT i;