evolution of transaction with need to get master data available for each subsequent stages

[Levente_Szittya]

Hi All,

I have a project where I shall create a report enabling end users to monitor the evolution of a transaction. Start point can be an offer to customer, or a purchase order from a customer. 2 more stages shall be monitored: shipments and invoices.

All 4 stages (offer - PO - shipment - invoice) share same criterion of related product.

On top of monitoring the evolution I would like to be able to show an overall anaylsis on products. Filtering to certain product I shall be able to see all offers, all POs, all shipment and all invoices.

How could this be carried out in such an evrinoment (like Qlik) where star-scheme data model is a must?

I have decided to link master table to purchase orders table. The challenge here is that there are cases when offer is not linked to any purchase orders... With such linkage when I filter to a product I can not get back those records from offer table which has no purchase order relations.

I have tried to define a set, but that ended up in a controversial solution: 

Sum({1<offer_part=P({$<offer_id=>}master_part)>} offer_qty)

Set expression I could define works properly if master data (ie product code) is filtered. But when I filter in offer table the set goes corrupted.

Starting from clean state: no selections yet:

- when I filter offer_id P() expression gives back all possible master_parts taking into account the entire data model

- with such result outer set can be written as {1<offer_part = { list of all possible master items }>}

- which is actually identical with a simple {1} set expression

How can I create a set expression that works properly with all selections: not only with those made on master data table but also with selections on offer_id, or key field between offer table and puchase order table (PO number)?

Thanks in advance,

Levente

[chris_djih]

I would suggest stacking up you fact tables in to a single big one.
using a 'FactType'. for example if you formerly had 3 tables:

purchase:

PurchaseID

productgroup

productID

amount

price

shipment:

ShipmentID

productgroup

productID

amount

price

Invoice:

InvoiceID

productgroup

productID

amount

price

Concatenate them together with a FactType to distinguish afterwards:

SalesFacts:
   Load PurchaseID as DocumentID,
   productgroup,
   productID,
   amount,
   price,
   'P'  as FactType
resident Purchase;

Concatenate(SalesFacts)
Load ShipmentID as DocumentID,
   productgroup,
   productID,
   amount,
   price,
   'S'  as FactType
resident Shipments;

Concatenate(SalesFacts)
Load InvoiceID as DocumentID,
   productgroup,
   productID,
   amount,
   price,
   'I'  as FactType
resident Invoice;

drop tables Purchase,Shipments,Invoice;

Then you can filter using set analysis: {FactType='P'} to only see purchases.

[Levente_Szittya]

Hi Chris,

Thanks for your answer, BUT! Evolution monitoring is a must. Filtering to order_id shall identify:

- if there is a PO linked

- if there is a shipment linked

- if there is an invoice linked

and vica-versa...

Stages shall remain in discrete tables. Concatenating them would result a very unwanted state  - I believe.

Still I need a solution to filter parts in all stages without loosing any records due to star-scheme.

Levente