topic Re: Odd behavior in Date Calculation in QlikView

Odd behavior in Date Calculation

Anonymous — Tue, 29 Nov 2016 20:03:18 GMT

Hi,

I have a table with three columns Date, FiscalYear and MonthName(Date). Sample data:

Date FiscalYear MonthName(Date)

42186 2016 Jul 2015

42187 2016 Jul 2015

Once I remove the MonthName(Date) column from the table, then the data immediately looks like:

Date FiscalYear

7/1/2015 12:00:00 AM 2016.000000000000000000

How that MonthName(Date) influence the format of other two columns(Date and FiscalYear). Please explain

Thanks

Re: Odd behavior in Date Calculation

MK_QSL — Tue, 29 Nov 2016 20:06:04 GMT

how you have calculated these fields?

Re: Odd behavior in Date Calculation

sunny_talwar — Tue, 29 Nov 2016 20:30:14 GMT

Would you be able to share your script with and without MonthName(Date)

Re: Odd behavior in Date Calculation

Anonymous — Tue, 29 Nov 2016 20:57:38 GMT

Hi Sunny,

I am pulling this from a QVD. By looking at the QVD, When I see the CALENDAR_DATE in the File Wizard, it is a number like 33585,33586 etc and Fiscal Year also a number like 1990 etc.

TempCalendar:

LOAD

CALENDER_DATE,

CALENDER_YEAR AS Year,

FISCAL_YEAR AS FiscalYear

// MonthName(CALENDER_DATE) AS [Month Year]

FROM

CALANDER_EXT.qvd(qvd);

If I load this QVD without the MonthName(CALENDER_DATE) AS [Month Year], then the CALENDER_DATE is getting converted to real date like 7/1/2015 12:00:00 AM and FiscalYear is getting converted to 2016.00000000000.

If I use the MonthName(CALENDER_DATE) AS [Month Year], then i am getting the same value as what i see in the file wizard of the QVD.

Thanks