Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
lauracastagna
Creator II
Creator II

using 3 dates in a table for 3 separate YearMonth Calendars.Qlikivew

Hi,

I have watched a few youtubes on this but still cant get it to work.  Any help appreciated. I need to use the 3 dates in this table in separate Charts.  At present the when I use the ”,SurveyCompDateKey,JobCompDateKey dates,  the chart still uses the UserDateKey.  Any help appreciated.

I have created the following table:

  QUALIFY*;
UNQUALIFY "%UserDateKey”,%SurveyCompDateKey",”%JobCompDateKey”;
Summary:
LOAD
Name,

Item,
SurveyCompDateKey as %SurveyCompDateKey,

  JobCompDateKey as %JobCompDateKey,
UserCreated as %UserDateKey
;
UNQUALIFY*;

SQL SELECT

Name,

Item,
convert(varchar,CareerQuizCompletedDate,103) as
SurveyCompDateKey,
convert(varchar,JobFitDiagnosticCompletedDate,103) as
JobCompDateKey ,
convert(varchar,UserCreated,103) as UserCreated,
FROM "Summary".dbo."SummaryUser"

Then I have created the following Calendars (I've only loaded two for this example:-

 

QuartersMap: 
MAPPING LOAD  
rowno() as Month
'Q' &
Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min(%UserDateKey) as minDate
max(%UserDateKey) as maxDate 
Resident UserSummary; 

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)

UserSummaryrCalendar: 
Load 
TempDate AS %UserDateKey
Week(TempDate) As Week
Year(TempDate) As Year
Month(TempDate) As Month
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'), 'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC
Drop Table TempCalendar;


//.....................................................Survey date…………………………………..

Temp: 
Load 
min(%SurveyCompDateKey) as minDate
max(%SurveyCompDateKey) as maxDate 
Resident UserSummary; 

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

UserSummaryrCompQuizzCalendar: 
Load 
TempDate AS %SurveyCompDateKey
Week(TempDate) As SurveyCompWeek
Year(TempDate) As SurveyCompYear
Month(TempDate) As SurveyCompMonth
Day(TempDate) As SurveyCompDay
ApplyMap('QuartersMap', month(TempDate), Null()) as QuizCompQuarter
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as QuizCompWeekYear,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'), 'MMMyyyy') as QuizCompYearMonth,
WeekDay(TempDate) as QuizCompWeekDay 
Resident TempCalendar 
Order By TempDate ASC
Drop Table TempCalendar; 

 

 

2 Replies
jolivares
Specialist
Specialist

Try to make a schema like this:

Capture.PNG

marcus_sommer
MVP & Luminary
MVP & Luminary