topic Re: Star Schema How to Join in QlikView

Star Schema How to Join

poooja — Wed, 27 Mar 2019 17:23:50 GMT

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

Re: Star Schema How to Join

dplr-rn — Wed, 27 Mar 2019 18:01:43 GMT

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

Re: Star Schema How to Join

poooja — Wed, 27 Mar 2019 18:29:21 GMT

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

Re: Star Schema How to Join

dplr-rn — Wed, 27 Mar 2019 18:36:14 GMT

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

Re: Star Schema How to Join

dplr-rn — Wed, 27 Mar 2019 18:57:33 GMT

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

Re: Star Schema How to Join

poooja — Thu, 28 Mar 2019 00:25:27 GMT

Hi,

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

Thanks!

Pooja

Re: Star Schema How to Join

dplr-rn — Thu, 28 Mar 2019 00:28:29 GMT

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.

Re: Star Schema How to Join

poooja — Thu, 28 Mar 2019 00:39:48 GMT

Hi,

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

Re: Star Schema How to Join

qliksus — Thu, 28 Mar 2019 12:20:52 GMT

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)

Re: Star Schema How to Join

poooja — Thu, 28 Mar 2019 14:22:52 GMT

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

Re: Star Schema How to Join

qliksus — Fri, 29 Mar 2019 10:03:08 GMT

Hi ,

Yes right , as you need a common dates for representing both the order and shipment