Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Create an AUTO CALENDAR TABLE IN QLIK

Guys, I'll will appreciate a lot for your support in advanced. Recently the company it is evaluating the purchase of BI tools, Of course, I'm more inclined to select qlik, than the other BI tools. In middle of this WAR PowerBI, hit me hard, when in a "soft" course I receive, we could create a "CALENDAR TABLE" with only one function:  CALENDARAUTO()... The first field create ALL THE RECORDS for the whole calendar year, from January First to December 31st. EASILY.

So, I need to know if there is a way to create a table in Qlik that through a FUNCTION create all the records of a calendar table. As you can SEE in the attached image.

 

Calendar Table Create Automatically from a FunctionCalendar Table Create Automatically from a Function

 

It is Possible to "duplicate" this process in qlik? (of course with qlik function).

The is a procedure to create that in qlik automatically? Or I have to create the table outside and the attached to the project?

Any help will be appreciate, please, help me to beat OTHERS BI tools... jejeje 🙂

regards

Pedro Garcia

(Sorry for my english, some time it not very good looking)

 

 

 

4 Replies
Highlighted
Creator
Creator

I dunno if there is a simple function, what i do have is this code:

 

//Here i get the min and max date from my datasource (in this case any table that has the dates you need) so that i establish a limit for how many dates are going to be generated

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

 

TempCal:
LOAD
Date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;

MasterCalendar: 
Load
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Week(TempDate,6) as Week, //The number 6 means that my week starts on sunday... i dont really remember
If(WeekDay(TempDate)='Lun',1,
If(WeekDay(TempDate)='Mar',2,
If(WeekDay(TempDate)='Mie',3,
If(WeekDay(TempDate)='Jue',4,
If(WeekDay(TempDate)='Vie',5,
If(WeekDay(TempDate)='Sab',6,
If(WeekDay(TempDate)='Dom',7))))))) as WeekNumber, 
WeekDay(TempDate) as DayWeekName,
TempDate as Date,
Num(TempDate) as DateNumber,
Num(Month(TempDate)) as MonthNumber,
Year(TempDate)&' - '&if(Num(Month(TempDate))<10,'0'&Num(Month(TempDate)), Num(Month(TempDate))) as Year_Month
Resident TempCal
Order By TempDate;

Drop Table TempCal;

 

There are some other fields that i use and i didn't include, this can be the most basic way to create a master calendar. You can add anything you want to it, for example, here i add the last two days of each month

Left Join(MasterCalendar)
Load Year, Month, Max(Day) as LastDay, Max(Day,2) as AlmostLastDay
Resident MasterCalendar
Group by Year, Month;

 

Another example here, how many saturdays every month has

Left Join(MasterCalendar)
Load Year_Month, sum(SumadorSabado) as CantidadSabados
Resident MasterCalendar
Group by Year_Month;

 

Ass you can see, there is not an especific function but you can do many things creating your own master calendar with code.

 

I hope this helps bro

 

Highlighted
Contributor III
Contributor III

Hi

in Qlik there are these options:

  1. Create a calendar manually, with only for the values in the date field, this means it's going to have 365 values for a whole year in the best case scenario (Exists records for every date).
  2. Create a calendar automatically using the "autocalendar" function in the script
  3. Create a calendar using an autogenerate table with a start date and an end date
  4. Use the library QlikView Components, or QVC where you can find a set of many functions that will help in your everyday developments

If you want to compare "function" vs "function", your best options are 2 and 4 in the load script, not in the layout.

Hope this helps.

Regards.

 

 

Highlighted
Creator
Creator

Hola! Mira, que yo sepa no hay como tal una funcion para crear automaticamente un calendario, yo lo llamo masterCalendar (asi me referire a el en el codigo). Lo que yo hago es que tengo una aplicacion que tiene un script que genera un masterCalendar y el archivo que crea esa aplicacion lo mando a llamar a todas donde lo voy a requerir, el archivo creado es un QVD (Si no sabes que son los QVD, revisalos, son muy utiles para la carga rapida de datos)

 

Mira, este es el codigo que yo utilizo para crear mi calendario

//Estas variables obtienen la fecha maxima y minima de alguna tabla que contenga fechas, en este caso pues deberian de ser las fechas que voy a usar, si tienes varias tablas con varios campos de fechas, ahi acomoda para obtener la mas vieja y la mas actual

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

 

//Aqui utilizo esas variables y genero una tabla que contiene todas las fechas desde la Min hasta la Max.

TempCal:
LOAD
Date($(vMinDate) + RowNo() - 1) as TempDate
AutoGenerate
$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

 

//Y aqui simplemente con el campo generado juego con la informacion de la manera que yo requiera.

MasterCalendar:
Load
Year(TempDate) as Año,
Month(TempDate) as Mes,
Day(TempDate) as Dia,
Week(TempDate,6) as Semana,
If(WeekDay(TempDate)='Lun',1,
If(WeekDay(TempDate)='Mar',2,
If(WeekDay(TempDate)='Mie',3,
If(WeekDay(TempDate)='Jue',4,
If(WeekDay(TempDate)='Vie',5,
If(WeekDay(TempDate)='Sab',6,
If(WeekDay(TempDate)='Dom',7))))))) as NumeroDiaSemana,
WeekDay(TempDate) as NombreDiaSemana,
TempDate as Fecha,
Num(TempDate) as FechaNumerica,
Num(Month(TempDate)) as MesN,
Year(TempDate)&' - '&if(Num(Month(TempDate))<10,'0'&Num(Month(TempDate)), Num(Month(TempDate))) as AñoMes
Resident TempCal
Order By TempDate;

Drop Table TempCal;

 

Como puedes ver, no es mucho codigo, se le pueden agregar las cosas que necesites, un ejemplo seria este

Left Join(MasterCalendar)
Load AñoMes, sum(SumadorSabado) as CantidadSabados
Resident MasterCalendar
Group by AñoMes;

 

//Ahi agrego por ejemplo, cuantos sabados tiene cada mes, quiza no todas las aplicaciones lo necesiten, pero hay algunas que si, entonces al momento de cargar la informacion a la aplicacion simplemente mandas a llamar los campos que necesitas. La ventaja de crearlo con codigo es que puedes generar los campos que necesites y mandarlos a llamar. Te dejo aqui la imagen del que yo genero para que veas la cantidad de cosas que se pueden agregar, aclarando que no todos son utilizados en todas las apps.

Capture.PNG

Espero y te ayude, saludos!

Highlighted
Contributor
Contributor

Lo reviso y te cuento como me fue, por ahora me da error en la siguiente linea, pero quizas no estoy invocando bien el nombre del campo en la tabla.

15:08:34 A
Autogenerate: generate count is negative: TempCal: LOAD Date( + RowNo() - 1) as TempDate AutoGenerate - + 1

 

 

Este es el Campo que estoy evaluando

LET vMinDate = Num(Peek(SAF_FECGRA, 0,SAF_FECGRA));
LET vMaxDate = Num(Peek(SAF_FECGRA, 0, SAF_FECGRA));

 

SAF_FECGRA