topic Re: Makedate function returning strange dates... in QlikView

Makedate function returning strange dates...

Tue, 30 Jun 2015 09:49:10 GMT

Hello

I am working on an excel sheet with different columns for a date , ie Year, month, day.

LOAD Year,
Month,
Day,
Hour,
Celsius,
Rain_mm,
Sun_Minutes,
MakeDate (Day&Month&Year) as Date

i have been trying using MAKEDATE to generate the date but in returns...i have this...01.01.112012, 01.01.122012, etc...

what i have done wrong ?

thanks

Re: Makedate function returning strange dates...

swuehl — Tue, 30 Jun 2015 09:49:59 GMT

MakeDate (Year, Month, Day) as Date

Re: Makedate function returning strange dates...

PrashantSangle — Tue, 30 Jun 2015 09:53:31 GMT

Hi,

go to help menu check the syntax of MakeDate()

Regards

Re: Makedate function returning strange dates...

Tue, 30 Jun 2015 09:55:52 GMT

thanks but now it is returning 01.01.2012...that it....;) any idea why ?

Re: Makedate function returning strange dates...

manojkulkarni — Tue, 30 Jun 2015 09:58:59 GMT

MakeDate(YYYY [ , MM [ , DD ] ] )

Returns a date calculated from the year YYYY, the month MM and the day DD.

If no month is stated, 1(January) is assumed.

If no day is stated, 1 (the 1:st) is assumed.

makedate(1999,2,14) returns 1999-02-14

Re: Makedate function returning strange dates...

Tue, 30 Jun 2015 09:59:06 GMT

Thanks max dreamer, i have started with this point

MakeDate(YYYY [ , MM [ , DD ] ] )

Returns a date calculated from the year YYYY, the month MM and the day DD.

If no month is stated, 1(January) is assumed.

If no day is stated, 1 (the 1:st) is assumed.

Examples:

makedate(1999) returns 1999-01-01

makedate(99) returns 0099-01-01

makedate(1992,12) returns 1992-12-01

makedate(1999,2,14) returns 1999-02-14

but this is not 100% clear...and working

best

Re: Makedate function returning strange dates...

swuehl — Tue, 30 Jun 2015 10:02:07 GMT

Could you post some sample records of your data? Year, Month, Day must have a numeric representation, i.e. Month is not just a text value like 'Jan'. But from what you've posted above, I would expect some dates returned like 01.01.2012.

Or are you asking about the format? You can change the format using the script variables, or Date() formatting function:

Date(MakeDate (Year, Month, Day),'YYYY-MM-DD') as Date

Re: Makedate function returning strange dates...

swuehl — Tue, 30 Jun 2015 10:03:03 GMT

but this is not 100% clear...and working

best

What's unclear? What is not working? You need to tell us the problems to help you.

Re: Makedate function returning strange dates...

sasiparupudi1 — Tue, 30 Jun 2015 10:07:49 GMT

what is the date format set in your script?

if you need it in a different format, use the date function to change the format

Date(MakeDate(Year, Month,Day),'YYYY-MM-DD') AS Date

Re: Makedate function returning strange dates...

PrashantSangle — Tue, 30 Jun 2015 10:11:20 GMT

Hi,

try below,

LOAD *,MakeDate(Year,Month,if(isnull(Day) or len(trim(Day))<1 ,'1',Day)) as makeDate INLINE [

Year, Month, Day

2015, 3, 1

2014, 2

2015, 3, 15

];

Regards

Re: Makedate function returning strange dates...

PrashantSangle — Tue, 30 Jun 2015 10:12:53 GMT

Hi,

If you want change the format use Date() as suggested by Swehl

Date(MakeDate(Year,Month,if(isnull(Day) or len(trim(Day))<1 ,'1',Day)),'DD/MMM/YYYY')

Regards

Re: Makedate function returning strange dates...

Tue, 30 Jun 2015 10:25:48 GMT

Year	Month	Day	Hour	Celsius	Rain_mm	Sun_Minutes
2012	1	1	0	5.6	0	0
2012	1	1	1	5.9	0	0
2012	1	1	2	5.8	0	0
2012	1	1	3	5.7	0	0
2012	1	1	4	5.4	0	0
2012	1	1	5	5.1	0	0
2012	1	1	6	5.5	0	0
2012	1	1	7	5.8	0.1	0
2012	1	1	8	6.7	0	0
2012	1	1	9	7.6	0.1	0
2012	1	1	10	8.5	0	0
2012	1	1	11	9.5	0	0
2012	1	1	12	10.2	0	1
2012	1	1	13	10.4	0	0
2012	1	1	14	10.8	0	0
2012	1	1	15	10.6	0	0
2012	1	1	16	9.5	0	0
2012	1	1	17	8.6	0	0
2012	1	1	18	7.9	0	0
2012	1	1	19	8	0	0
2012	1	1	20	7.3	0	0
2012	1	1	21	6.3	0	0
2012	1	1	22	6.1	0	0
2012	1	1	23	7.1	0	0 !

Re: Makedate function returning strange dates...

Tue, 30 Jun 2015 10:26:39 GMT

this is Jan 1 2012 full day, information on weather forecast hour by hour...

Re: Makedate function returning strange dates...

Tue, 30 Jun 2015 10:29:20 GMT

And need to transform this excel sheet in order to get this result : Day Month Year 01.01.2012

Re: Makedate function returning strange dates...

PrashantSangle — Tue, 30 Jun 2015 10:30:06 GMT

Hi,

What is your expected output????

Regards

Re: Makedate function returning strange dates...

sasiparupudi1 — Tue, 30 Jun 2015 10:36:21 GMT

LOAD *,

Date(MakeDate (Year,Month,Day),'DD.MM.YYYY') as Date

//MakeDate(Year, Month,Day) AS Date

INLINE [

Year,Month,Day,Hour,Celsius,Rain_mm,Sun_Minutes

2012,1,1,0,5.6,0,0

2012,1,1,1,5.9,0,0

2012,1,1,2,5.8,0,0

2012,1,1,3,5.7,0,0

2012,1,1,4,5.4,0,0

2012,1,1,5,5.1,0,0

2012,1,1,6,5.5,0,0

2012,1,1,7,5.8,0.1,0

2012,1,1,8,6.7,0,0

2012,1,1,9,7.6,0.1,0

2012,1,1,10,8.5,0,0

2012,1,1,11,9.5,0,0

2012,1,1,12,10.2,0,1

2012,1,1,13,10.4,0,0

2012,1,1,14,10.8,0,0

2012,1,1,15,10.6,0,0

2012,1,1,16,9.5,0,0

2012,1,1,17,8.6,0,0

2012,1,1,18,7.9,0,0

2012,1,1,19,8,0,0

2012,1,1,20,7.3,0,0

2012,1,1,21,6.3,0,0

2012,1,1,22,6.1,0,0

2012,1,1,23,7.1,0,0

];

Re: Makedate function returning strange dates...

Tue, 30 Jun 2015 10:36:50 GMT

One column with the date in the following format 01.01.2012 (day, month. year) and this result for each line of my weather forecast.

thanks for your support

Re: Makedate function returning strange dates...

Tue, 30 Jun 2015 10:36:55 GMT

As already suggested, I'd use Date(MakeDate(Year, Month,Day),'DD.MM.YYYY') AS Date.

Re: Makedate function returning strange dates...

swuehl — Tue, 30 Jun 2015 10:55:15 GMT

I understood that's what you already get?

Re: Makedate function returning strange dates...

buzzy996 — Tue, 30 Jun 2015 10:59:36 GMT

try tis way,

MakeDate (Day(Day)&Month(Month)&Year(Year)) as Date --i think it makes difference.

something like tihs exactly,

Makedate(Year([Order Date]),Month([Order Date]),Day([Order Date])) as DayMonthYear,