Conversion of Date

[marco_puccetti]

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

[pokassov]

Hi!

AUTOGENERATE 1 

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

[Gabriel]

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;

[rajkumarb]

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);

[marco_puccetti]

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

[pokassov]

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);

[marco_puccetti]

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

Thanks

[#8]

hi,

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

Regards

Vimlesh

[marco_puccetti]

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;

[#10]

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