grup different table by date and item

[bilionut]

HI,

The data is structured as follows

Orders:
LOAD * INLINE [
    OrderID, OrderDate
    101, 1/2/2014
    102, 1/2/2014
    103, 3/3/2014
];
OrdersDetails:
//Inner Join (Orders)
LOAD * INLINE [
    OrderID, ItemID, OrderQty
    101,    15,        111
    101,    16,        250   
    102,    17,        333
    103,    15,        100
];

MfgOrders:
LOAD * INLINE [
    MfgID, MfgOrderDate
    5101, 1/2/2014
    5102, 2/2/2014
    5103, 3/3/2014
];
//Inner Join (MfgOrders)
MfgOrdersDetails:
LOAD * INLINE [
    MfgID, ItemID, MfgOrderQty
    5101,    15,        222
    5101,    16,        300   
    5102,    17,        333
    5103,    18,        250   
];

//Inner Join (Orders)
Items:
Load * INLINE [
    ItemID,    Item
    15,    Aaaa
    16,    Bbbb
    17, Cccc
    18,    Dddd   
];

I try to make a table or a pivot table grouped first by date and then by item
something like this

Date	Item	OrderQTY	MfgQTY
1/2/2014	Aa	111	222
1/2/2014	Bb	250	300
1/2/2014	Cc	333	------
2/2/2014	Cc	------	333
3/3/2014	Aa	100	------
3/2/2014	Dd	------	250

Any idea?

Thanks for your help!

[swuehl · Accepted solution]

You can create a common fact table like this, then create a simple straight table with dimensions Date and Item and expressions sum(OrderQty) and sum(MfgQty):

Orders:

LOAD * INLINE [

    OrderID, OrderDate

    101, 1/2/2014

    102, 1/2/2014

    103, 3/3/2014

];

OrdersDetails:

Inner Join (Orders)

LOAD * INLINE [

    OrderID, ItemID, OrderQty

    101,    15,        111

    101,    16,        250   

    102,    17,        333

    103,    15,        100

];

MfgOrders:

LOAD * INLINE [

    MfgID, MfgOrderDate

    5101, 1/2/2014

    5102, 2/2/2014

    5103, 3/3/2014

];

Inner Join (MfgOrders)

MfgOrdersDetails:

LOAD * INLINE [

    MfgID, ItemID, MfgOrderQty

    5101,    15,        222

    5101,    16,        300   

    5102,    17,        333

    5103,    18,        250   

];

FACT:

LOAD *,OrderDate as Date Resident Orders;

CONCATENATE LOAD *, MfgOrderDate as Date Resident MfgOrders;

drop tables Orders, MfgOrders;

//Inner Join (Orders)

Items:

Load * INLINE [

    ItemID,    Item

    15,    Aaaa

    16,    Bbbb

    17, Cccc

    18,    Dddd   

];

[bilionut]

Hi swuehl,

Thank you very much for your help. It's work fine.

I have only one question: how could I do in this case decoding MONTH or YEAR from field Date?

Usually i do something like that at Load from table:

MONTH(Date) as Month,

YEAR(Date) as Year,

but now I'm a little confused about the correct syntax.

Thanks again!

[anbu1984]

You can use Month and Year function in this case also

FACT:

LOAD *,OrderDate as Date,Month(OrderDate) as OrderDateMonth,Year(OrderDate) as OrderDateYear Resident Orders;

CONCATENATE LOAD *, MfgOrderDate as Date Resident MfgOrders;

[swuehl]

You can look into the master calendar concept (search the forum for master calendar, there are tons of examples), linking the master calendar table to the Date field, or duplicating the calendar and link it to all date fields needed.

[bilionut]

Hi swuehl,

I was able to creating a pivot according to your response solution.

Fact table looks like that

But how could I load a table with fields MfgOrderQty and OrderQty  on the same row, group by Date and ItemID