topic Re: Conversion of Date in QlikView

Conversion of Date

marco_puccetti — Fri, 10 Apr 2015 09:40:42 GMT

Hello i need to create a calendar to navigate the date contained in the tables.

Is it possible to create this using this approach:

1) Compute the max date;

2) Compute the min date;

3 Create the calendar using the autogenerate from the min date to the max date, increasing the numeric version of the date each row generation.

As regard as the step 3 i need a function to convert a Simple data in the form dd-mm-yyyy to an integer value.

Using this

load

Date($(m_rif_date_min) + RowNo()-1) As DT_RIFERIMENTO

autogenerate($(m_rif_date_max)-$(m_rif_date_min)+1);

It doesn't work.

Thanks

Marco

Re: Conversion of Date

pokassov — Fri, 10 Apr 2015 09:44:09 GMT

Hi!

AUTOGENERATE 1

WHILE $(m_rif_date_min)+IterNo()-1<= $(m_rif_date_max);

Re: Conversion of Date

Gabriel — Fri, 10 Apr 2015 09:49:23 GMT

Hi,

Try this

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(m_rif_date_min) as minDate,

max(m_rif_date_max) as maxDate

Resident Orders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: Conversion of Date

rajkumarb — Fri, 10 Apr 2015 09:55:31 GMT

HI Marco

Try Like this

TempCalendar:

NoConcatenate

LOAD

$($(m_rif_date_min)) + Iterno()-1 As Num,

Date($(m_rif_date_min) + IterNo() - 1) as DT_RIFERIMENTO

AutoGenerate 1 While $(m_rif_date_min) + IterNo() -1 <=$(m_rif_date_max);

Re: Conversion of Date

marco_puccetti — Fri, 10 Apr 2015 10:14:37 GMT

It not properly works until it is used the $(date) + number expression.

I expect to use this form to generalize the generation of the date is it possible?

Thanks

Marco

Re: Conversion of Date

pokassov — Fri, 10 Apr 2015 11:24:19 GMT

let m_rif_date_min=MakeDate(2013,1,1);

let m_rif_date_max=MakeDate(2015,1,1);

t1:

load

Date('$(m_rif_date_min)' + RowNo()-1) As DT_RIFERIMENTO

autogenerate('$(m_rif_date_max)'-'$(m_rif_date_min)'+1);

Re: Conversion of Date

marco_puccetti — Tue, 14 Apr 2015 10:54:13 GMT

Is this possible in case of use of dates with this format: dd-mm-yyyy?

Thanks

Re: Conversion of Date

Tue, 14 Apr 2015 11:15:38 GMT

hi,

for auto generate of date ,it should be in numeric (date ) format.

Regards

Vimlesh

Re: Conversion of Date

marco_puccetti — Tue, 14 Apr 2015 12:19:04 GMT

I use this script:

TEMP_DATA:

LOAD

Min(makedate(Mid(DATA_PRES,7,4),Mid(DATA_PRES,4,2),Mid(DATA_PRES,1,2))) AS DATA_RIF_MIN,

Max(makedate(Mid(DATA_PRES,7,4),Mid(DATA_PRES,4,2),Mid(DATA_PRES,1,2))) AS DATA_RIF_MAX

RESIDENT

PRESENTAZIONI;

Let m_rif_date_min = Peek('DATA_RIF_MIN',0,'TEMP_DATA');

Let m_rif_date_max = Peek('DATA_RIF_MAX',0,'TEMP_DATA');

DROP TABLES TEMP_DATA;

Calendario:

load

//Date($(m_rif_date_min) + RowNo()-1) As DT_RIFERIMENTO

($(m_rif_date_min) + RowNo()-1) As DT_RIFERIMENTO

autogenerate($(m_rif_date_max)-$(m_rif_date_min)+1);

I need also to obtain the min and max date from different tables, so what should i change in these few lines of code:

TEMP_DATA:

LOAD

Min(makedate(Mid(DATA_PRES,7,4),Mid(DATA_PRES,4,2),Mid(DATA_PRES,1,2))) AS DATA_RIF_MIN,

Max(makedate(Mid(DATA_PRES,7,4),Mid(DATA_PRES,4,2),Mid(DATA_PRES,1,2))) AS DATA_RIF_MAX

RESIDENT

PRESENTAZIONI;

Re: Conversion of Date

Wed, 15 Apr 2015 04:45:09 GMT

hi,

You will change variable Name for them and Date name(through alias) only.

one thing you can do make a common date calendar after creating all date.

Regards

Vimlesh

Re: Conversion of Date

marco_puccetti — Wed, 15 Apr 2015 08:14:26 GMT

Thanks

Marco