topic Re: grup different table by date and item in QlikView

grup different table by date and item

bilionut — Thu, 01 May 2014 09:37:14 GMT

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!

Re: grup different table by date and item

swuehl — Thu, 01 May 2014 09:45:42 GMT

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

];

Re: grup different table by date and item

bilionut — Mon, 05 May 2014 12:53:28 GMT

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!

Re: grup different table by date and item

anbu1984 — Mon, 05 May 2014 13:30:06 GMT

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;

Re: grup different table by date and item

swuehl — Mon, 05 May 2014 15:13:17 GMT

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.

Re: grup different table by date and item

bilionut — Thu, 15 May 2014 11:37:57 GMT

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