topic Re: need update day in QlikView

need update day

Mon, 20 Oct 2014 10:57:54 GMT

hi,

I have data table including year & month fields. i need create date filed considering month end date in script . please help me.

Re: need update day

anbu1984 — Mon, 20 Oct 2014 11:02:51 GMT

MonthEnd(MakeDate(Year,Month,1))

Re: need update day

Mon, 20 Oct 2014 11:35:54 GMT

hi anbu,

error occurred in the data loading

i enclosed sample date please help.

year	month	date
2011	1
2011	2
2011	3
2011	4
2011	5
2011	6
2012	1
2012	2
2012	3
2012	4
2012	5
2012	6
2013	1
2013	2
2013	3
2013	4
2013	5
2013	6

Thx

Re: need update day

anbu1984 — Mon, 20 Oct 2014 11:41:35 GMT

Load *,MonthEnd(MakeDate(year,month,1)) As Date Inline [

year,month

2011,1

2011,2

2011,3

2011,4

2011,5

2011,6

2012,1

2012,2

2012,3

2012,4

2012,5

2012,6

2013,1

2013,2

2013,3

2013,4

2013,5

2013,6 ] ;

Re: need update day

PradeepReddy — Mon, 20 Oct 2014 11:43:14 GMT

try something like this..

Temp_Data:

Load * inline

[

Year,Month

2011,1

2011,2

2011,5

2012,7

2012,8

];

Main_data:

load *,

monthend(makedate(Year,Month,1)) As Month_End

resident Temp_Data;

Drop table Temp_Data;

Re: need update day

ashfaq_haseeb — Mon, 20 Oct 2014 11:45:40 GMT

Like this

Load*,MonthEnd(MakeDate(year,month,1)) as Date;

LOAD year,

month

// date

FROM

[http://community.qlik.com/thread/138803]

(html, codepage is 1252, embedded labels, table is @1);

Have a look at attached document

Regards

ASHFAQ

Re: need update day

PrashantSangle — Mon, 20 Oct 2014 11:52:23 GMT

Hi,

If you want Month End Date

Then Try like

MonthEnd(MakeDate(Year,Month)) AS Month_End

If you want to change the format

USe date()

date(MonthEnd(MakeDate(Year,Month)),'DD-MMM-YYYY')

Regards

Re: Re: need update day

MarcoWedel — Mon, 20 Oct 2014 22:52:27 GMT

Hi,

if you want real dates (integer, non fractional values, i.e. time = 00:00:00), then you have to purge the time values e.g. like:

LOAD year,
    month,
    Date(Floor(MonthEnd(MakeDate(year,month)))) as date
FROM [http://community.qlik.com/thread/138803] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: need update day

MarcoWedel — Thu, 23 Oct 2014 05:22:58 GMT

Instead of floor() you can also use daystart() or dayname().

Hope this helps also

Regards

Marco

Re: need update day

Thu, 23 Oct 2014 11:44:32 GMT

Thx