Star Schema How to Join

[poooja]

Hi Qliks,

Need an help to build Star Schema

Order table has orderdate, customer num, cust name, itemnumber, order num, ordered qty.
Shipped table has shipped date, order date, order num, item num,item description, shipped qty, cust num, cust name.

 How to join them to get :

1. ordered qty vs shipped qty by item description
2. Weekly monthly ordered qty vs shipped qty

Any idea is appreciated.

Thanks!

Pooja

[dplr-rn]

More general opinion as i am not sure about how your data is arranged
i would design it in this way order--> orderitem
orderitem can contain the shipped details as well as ordered quantity.
on initial thought that should cover #1 and be flexible

for item # 2 as you have 2 date fields think of something like a canonical date
https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

p.s. by coincidence the data model hic describes in the above link is similar to what i was mentioning in the top

[poooja]

Hi ,
For the option 1 we have to get ordered qty vs shipped qty by item description but our order table has only item num. How to match these?

Thanks!
Pooja

[dplr-rn]

you have to convert you table into the structure mentioned above.
You dont have an order id? that should be the link between order and orderlineitems table

[dplr-rn]

use ordernumber as the unique identifier

going from column names you mentioned

below is rough approach (logic should be sound not the syntax)

Order:
load
distinct
OrderNum //Joining key to order line details
,orderdate
,customer num
, cust name
from ordersource;

OrderLineItems:
load
itemnumber, ordernum, ordered qty
from ordersource;

left join(OrderLineItems) //Joining key to order line details will be order num and item num
load
 ordernum
, item num
,item description
, shipped qty
, shipped date
from ShippedDetailsSource

result is tables

Order table - OrderNum  ,orderdate ,customer num , cust name

OrderLineItems - itemnumber, ordernum, ordered qty, item description,shipped qty, shipped date

[poooja]

Hi,

I am bit confused that we are missing few columns. PFA Source for your reference.

Thanks!

Pooja

[dplr-rn]

I don't know your full need.
So Trying to point you in the right direction rathet than giving you some answer which may not fit the business need.

[poooja]

Hi,

I am trying to remove the synthetic key here and also need to find the below solution. 

[qliksus]

You can also use intervalmatch to your case of month wise Ordered and Shipping quantity

Shipped:
LOAD [Cust Num],
     [Cust Name],
     ShipAddr,
     ShipCity,
     OrderNum,
     OrderDate,
     num(month(OrderDate)) as start,
     ExpectedDate,
     ItemNum,      
     ItemDesc,
     ShippedDate,
     if( isnull(num(month(ShippedDate))),num(month(OrderDate)),num(month(ShippedDate))) as End,
     num(month(OrderDate))&if( isnull(num(month(ShippedDate))),num(month(OrderDate)),num(month(ShippedDate))) as Key,
     ShippedQty,
     NetAmt,
     Weight,
     Carrier
    
FROM
Order_Shipped.xlsx
(ooxml, embedded labels, table is Shipped);

QUALIFY * ;

UNQUALIFY OrderNum,Key;

Order:
LOAD [Cust Num],
     [Cust Name],
     Addr,
     City,
     OrderNum,
     OrderDate,
     ItemNum,
    
     ShippedDate,
     OrderedQty,
     Status
FROM
Order_Shipped.xlsx
(ooxml, embedded labels, table is Order);

UNQUALIFY * ;

load

IterNo() as Month
autogenerate(1)
While IterNo()-1<12 ;

calendar1:
IntervalMatch(Month)
 load start,End
Resident Shipped ;

calendar:
load

Month,
start&End as Key
Resident calendar ;

DROP Table calendar1 ;

And create a chart with

Dimension : Month

Expression1:  Sum ( ShiipedQty)

Expression2:  Sum ( order.OrderedQty)

[poooja]

Hi,

If this is the case - are we need to create one more calendar for find weekly & yearly values?

Thanks Much for your help.

Pooja