Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi,
I am bit confused that we are missing few columns. PFA Source for your reference.
Thanks!
Pooja
Hi,
I am trying to remove the synthetic key here and also need to find the below solution.
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)
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