topic Re: Making Master Calender in QlikView

Making Master Calender

alkesh_sharma — Thu, 06 Mar 2014 10:39:53 GMT

Hello,

I have a data in which the date format is as below:

1-JAN_2013

2-JAN_2013 and so on...

How can i change it in standard format as the Year(), Month() commands are not working on the mentioned format.

Thanx in Advance

Re: Making Master Calender

Thu, 06 Mar 2014 10:45:16 GMT

Look at your default set statements for dates

Mine are

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

To use your format change set statements to

SET DateFormat='D-MMM_YYYY';

SET TimestampFormat='D-MMM_YYYY h:mm:ss[.fff] TT';

SET MonthNames='JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Re: Making Master Calender

MarcoWedel — Thu, 06 Mar 2014 10:45:28 GMT

replace the original DateFormat in the load script with

SET DateFormat='D-MMM_YYYY';

regards

Marco

Re: Making Master Calender

MK_QSL — Thu, 06 Mar 2014 10:48:11 GMT

Check enclosed file..

Re: Making Master Calender

sudeepkm — Thu, 06 Mar 2014 10:50:16 GMT

Please take a look at the attachment.

Re: Making Master Calender

msteedle — Thu, 06 Mar 2014 13:34:35 GMT

As an alternative to the formatting approach, you can convert it when loading it using the date conversion function, DATE#.

Date#([YourDateField], 'D-MMM_YYYY') as [YourDateField]

Re: Making Master Calender

alkesh_sharma — Fri, 07 Mar 2014 03:26:39 GMT

Thanx for your suggestions but this approach is not working...:(

Re: Making Master Calender

jagan — Fri, 07 Mar 2014 04:40:39 GMT

Hi,

Can you attach sample file that you are working with some data.

Regards,

Jagan.

Re: Making Master Calender

ashwanin — Fri, 07 Mar 2014 08:26:07 GMT

Hi Akhlesh,,

Please add below in your script to make Year, Month and Day. Here i have taken Date as field where the date format '1-JAN_2013' exists.

Left(Date,1) as Day,

right(Date,4) as Year,

Mid(Date,Index(Date,'-',1)+1,3) As Month,

Re: Making Master Calender

ashwanin — Fri, 07 Mar 2014 08:54:55 GMT

and in case User has used the month field like July i/o JUL the put the below formula for Month field.

Mid(Date,Index(Date,'-',1)+1,Index(Date,'_',1)-3) As Month,

Re: Making Master Calender

er_mohit — Fri, 07 Mar 2014 08:59:28 GMT

try this

load*,

year(Date) as Year,

Month(Date) as Month;

load

Date(Date#(Purgechar(Datefield,'_,-'),'DDMMMYYYY'),'DD-MMM-YYYY') as Date

FieldA,Fieldb,

....

..so on

from table;