topic Relationship Anomoly? in QlikView

Relationship Anomoly?

Thu, 09 Dec 2010 17:35:50 GMT

I am new to Qlikview and still practising my skills. I decided to apply Qlikview to Microsofts Northwind database for practise purposes. Amongst other tables I created an Order and OrderDetails Table - each with an added calculated column OrderValue and LineTotal respectively. (Original - I know!). I created a pivot table of with Customer and Year as Dimensions and (initially) Sum(LineTotals) and later Sum(OrderValue). In both cases the pivot table displayed most of the rows exactly as expected but for some customers it created a blank year column and placed values in there. More investigation showed that these were the values from one or more Orders from the last year (1994) even though the Orders clearly had a 1994 orderdate. Any clues as to what is going on here? All the Order Details have corresponding Orders and v.v.

Relationship Anomoly?

Thu, 09 Dec 2010 17:56:01 GMT

Can you post your QVD? It may be easier to troubleshoot if we can take a look at what you're doing.

EDIT: Yeah, sorry, I meant the QVW. Thanks Oleg!

Relationship Anomoly?

Thu, 09 Dec 2010 19:39:39 GMT

My version of Qlikview is currently Personal edition and it does not appear to let me export a QVD for the application but I have exported the individual tables as QVDs. Unfortunately this site tells me that .qvd and .qvw files are not allowed when I try to attach them.

Relationship Anomoly?

Oleg_Troyansky — Thu, 09 Dec 2010 20:22:27 GMT

It's the QVW that we need, not the QVD (I believe NMiller had a typo...)

You should be able to upload a QVW, everyone does. Open the "Options" tab and click on "Add/Update" button on top.

Relationship Anomoly?

Fri, 10 Dec 2010 10:20:04 GMT

Thanks. Attached.

Relationship Anomoly?

Fri, 10 Dec 2010 17:13:28 GMT

I have to assume that the anomoly has something to do with the OrderDate. I attach an analysis which shows that all the orders whose OrderValues get pushed into the blank year column have OrderDates between 24/03/1994 and 31/03/1994 inclusive. If a customer has 2 orders in that date range they are summed up in the blank year column. Any that lie outside that range are summed up within their years exactly as expected. I am still struggling to make sense of this.

Relationship Anomoly?

Fri, 10 Dec 2010 17:42:44 GMT

I think it has something to do with your temp calendar. If, in your Customer Annual Sales chart, you replace the Year dimension with Year(OrderDate), you get rid of the blank year.

Could you try to reload the data without dropping the TempCalendar table? I'm guessing there is something happening there so that the dates aren't appearing in the MasterCalendar.

If you look at the value of your vMaxDate variable, it is set to 23/03/1994. Your TempCalendar is only generating through that date, which means there are no dates in the Master Calendar that match up with the missing dates. You may even be having a problem with date format since only 24/04/1994 and up isn't appearing.

EDIT: Check out the main tab of your load. You have:

SET DateFormat='DD/MM/YYYY';

Modify that for the date format you are using.

Relationship Anomoly?

Oleg_Troyansky — Mon, 13 Dec 2010 19:31:37 GMT

The problem, as I see it, is that your Master Calendar is missing 5 dates, and those seem to be your problematic dates:

OrderDate	Year	Month
24/03/1994
25/03/1994
28/03/1994
29/03/1994
30/03/1994

OrderDate	Year	Month
24/03/1994
25/03/1994
28/03/1994
29/03/1994
30/03/1994

Check your logic of generating the Master Calendar

Relationship Anomoly?

Tue, 14 Dec 2010 12:08:55 GMT

Thank you to both yourself and Oleg for your reponses. I have "hard-coded" the max and min dates which eliminated the problem. It does beg the question as to why the pieces of code:

LET

Did not appear to pick up the Lowest and Highest OrderDates - or at least the Highest one.

varMaxDate = Num(Peek('Orders.OrderDate',-1,'Orders')); varMinDate = Num(Peek('Orders.OrderDate',0,'Orders'));

Relationship Anomoly?

Tue, 14 Dec 2010 12:16:07 GMT

Sorry - let me try again this time using the suggestion to copy in via Word:

LET varMinDate = Num(Peek('Orders.OrderDate',0,'Orders'));

LET varMaxDate = Num(Peek('Orders.OrderDate',-1,'Orders'));

Relationship Anomoly?

Tue, 14 Dec 2010 12:54:46 GMT

The hard coding generates a lot of unused dates but the following appears to work and do what the original code was supposed to do:

Period:

LOAD Min(OrderDate) as StartDate,

Max(OrderDate) as EndDate

RESIDENT Orders;

LET varMinDate = Num(Peek('StartDate',0,'Period'));

LET varMaxDate = Num(Peek('EndDate',0,'Period'));

LET varToday = num(Today());

//*************************** Temporary Calendar****************

TempCalendar:

LOAD

$(varMinDate) + rowno() - 1 as Num,

date($(varMinDate) + rowno() -1) AS TempDate

AUTOGENERATE

$(varMaxDate)-$(varMinDate)+1;

MasterCalendar:

LOAD TempDate as OrderDate,

week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

WeekDay(TempDate) as WeekDay,

'Q'&ceil(Month(TempDate)/3) AS Quarter,

Date(monthstart(TempDate),'MMM-YYYY') As MonthYear,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

inyeartodate(TempDate,$(varToday),0)*-1 AS CurYTDFlag,

inyeartodate(TempDate,$(varToday),-1)*-1 AS LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;