"Crossatab query" in qlikview

[maxronny]

hi friend,

i wanna realize a crosstab in which, for the year/month selected, i can have the name of employees in rows and the single days in columns, and the number (always 1 of course) of the holiday, like this:

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
rossi		1	1	1	1
verdi							1	1	1
mc donald																												1	1	1	1
mc carter																		1	1	1	1										1

I tried many ways to get it but i surrendered....i attached the qwv file.

Thank you in advance for you attention.

Massimo.

[MarcoWedel]

Hi,

maybe one solution could be:

Assenze_View:
LOAD RecNo() as ID,
     cognome,
     nome,
     cognome&' '&nome as Nominativo,
     inizio,
     fine,
...;
SQL SELECT *
...;

tabDateLink:
LOAD ID,
     Date(inizio+IterNo()-1) as Date
Resident Assenze_View
While inizio+IterNo()-1 <= fine;

tabCalendar:  
LOAD *,  
     Day(Date) as Day,  
     WeekDay(Date) as WeekDay,  
     Week(Date) as Week,  
     WeekName(Date) as WeekName,  
     Month(Date) as Month,  
     MonthName(Date) as MonthName,  
     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,  
     QuarterName(Date) as QuarterName,  
     Year(Date) as Year,  
     WeekYear(Date) as WeekYear;    
LOAD Date(MinDate+IterNo()-1) as Date  
While MinDate+IterNo()-1 <= MaxDate;  
LOAD Min(Date) as MinDate,  
     Max(Date) as MaxDate  
Resident tabDateLink;

hope this helps

Marco