Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Table data not linked properly.

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.

1 Solution

Accepted Solutions
Not applicable

Table data not linked properly.

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.

4 Replies
richard_chilvers
Contributor III

Table data not linked properly.

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

Not applicable

Table data not linked properly.

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

richard_chilvers
Contributor III

Table data not linked properly.

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

Not applicable

Table data not linked properly.

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.

Community Browser