topic Re: Date format problem in QlikView

Date format problem

Sun, 11 Nov 2012 20:05:36 GMT

Hello

i have a ticket date from a xml file, i will split the date in year,week,month,day

i am using the mastercalander script;

he shows in the frontent example;

year 56989 = 2012

date 20120602

script master clender

Let varMinDate = NUM(PEEK('OrderDate' , 0, 'Orders'));

Let varMaxDate = NUM(PEEK('OrderDate' , -1, 'Orders'));

Let varToday = NUM(Today());

Let vJulianStartDate = Date(1);

// Important note, always check variables before you continue!

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 TempCalenda

Re: Date format problem

swuehl — Sun, 11 Nov 2012 22:24:47 GMT

Sorry, I haven't got your issue.

It might be helpul if you could post your complete script, including the loading of your Orders table (or even better, post a small sample qvw app).

Re: Date format problem

Mon, 12 Nov 2012 04:32:16 GMT

r u looking for :

SET DateFormat='DD/MM/YYYY';

on the Main tab

Re: Date format problem

swuehl — Mon, 12 Nov 2012 08:30:55 GMT

Yes, as I assumed, the key to your issue is in your Orders table load.

You need to tell QV to interprete 20120602 as Date, not as number (or as the numeric representation of a number, which will then result in a date in year 56988 (20,120,602 days since 30 Dec 1899) ).

When loading in your data, either set your standard date format to 'YYYYMMDD', or explicitely tell QV how to interpret your data using date#() function:

Orders:

LOAD

Date#(Date,'YYYYMMDD') as OrderDate,

Time,

WhoNr,

WhoName,

PcNr,

EcoNr,

EcoName,

PriceTag,

TaPriceTag,

%Key_Ticket_369F651F33B57014, // Key to parent table: Tickets/Ticket

%Key_Order_2A9BFA2C52FE6F55 // Key for this table: Tickets/Ticket/Order

FROM FX20120602.xml (XmlSimple, Table is [Tickets/Ticket/Order]);

Re: Date format problem

Mon, 12 Nov 2012 09:49:54 GMT

Hello Stefan

Thanx

I will show the date ddmmyyyy, i can simply the script change in this format ?

I am a starter in scripting Qlikview it's a long way to use the scipt correctly.

Thanx !!

Anton

Re: Date format problem

swuehl — Mon, 12 Nov 2012 09:54:18 GMT

If this is your standard date format, define it as such in the script, changing the line at the top of your script:

SET DateFormat='DDMMYYYY';

You'll still need to use the interpretation function date#() to read in your data. To format your date according your standard format, just use an additional date() function (note the subtile difference between date#() and date(), check the HELP for more details):

Orders:

LOAD

Date(Date#(Date,'YYYYMMDD')) as OrderDate,

Time,

WhoNr,

WhoName,

...