topic Table data not linked properly. in QlikView

Table data not linked properly.

Thu, 03 Nov 2011 16:05:38 GMT

Hi Guys,

My first time posting here so take it easy on me.

I have been having an issue with qlikview where the data I load is not properly being linked.

I have included an example file and a image of the problem.

previously, I had been using the following select statements to load the date data but qlikview was not recognising it as a date:

TO_CHAR(DATE_ORDER, 'DD') as DAY,

TO_CHAR(DATE_ORDER, 'MON') as MONTH,

TO_CHAR(DATE_ORDER, 'MM/YYYY') as YEARMONTH,

TO_CHAR(DATE_ORDER, 'YYYY') as YEAR,

TO_CHAR(DATE_ORDER, 'Q') as QUARTER,

Now that I am using "LOAD Distinct" the dates are correctly loaded and sorted as dates but they are not linked to the table data.

Please help.

Thanks

Paul Maillet

p.s.: Using an Oracle 10g DB.

Table data not linked properly.

richard_chilvers — Thu, 03 Nov 2011 16:47:48 GMT

Hi Paul

There are a few things I don't quite understand about your app.

But the first thing I would try is using the TO_DATE function instead of the TO_CHAR function.

Regards

Table data not linked properly.

Thu, 03 Nov 2011 17:03:00 GMT

I'm afraid I don't understand, the data is already in a DATE format.

Table data not linked properly.

richard_chilvers — Thu, 03 Nov 2011 17:11:41 GMT

OK - well, if you're sure of that.

In my experience, dates can be handled in various ways, depending on the data structure.

I was thinking your date may be a string which will best be handled by the TO_DATE function. For the sake of a tiny change and RELOAD, it may be worth it

Table data not linked properly.

Thu, 03 Nov 2011 18:18:41 GMT

Paul,

Which table do you want InvoiceView link to? For link with table you need a same column name so propably you need to change you first load to:

LOAD Distinct DATE_ORDER as INVOICE_DATE

Month(DATE_ORDER) as Month,

Date(Monthstart(DATE_ORDER), 'MMM-YYYY') as YearMonth,

'Q' & Ceil(Month(DATE_ORDER)/3) as Quarter,

Dual(Year(DATE_ORDER) & '-Q' & Ceil(Month(DATE_ORDER)/3), Year(DATE_ORDER) & Ceil(Month(DATE_ORDER)/3)) as YearQtr,

Week(DATE_ORDER) as Week;

Kiran.