Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating a Resident Load list for a SQL Query

Hi I'm looking to sync up my calender with my code below, however I am not sure what I am missing in order for them to link:

Script for Main:

ARRequestList:

SQL SELECT rq.request_id as [Request ID],

       rq.form_id as [Form ID],

       rtrim(fh.short_name) +

       case when

       ( select max(x.user_type)

           from requestuser x

          where x.request_id = rq.request_id ) = 'T' then ' (Transfer)'

         else ''

       end as [Form Name],

       rq.user_id as [Create User ID],

       rq.user_name as [Create User Name],

       rq.create_date as [Create Date],

       rq.date_submit as [Submit Date]

  from FormProd.dbo.request rq

       inner join FormProd.dbo.requestlist rl on

       ( rq.request_id = rl.request_id )

       inner join FormProd.dbo.formheader fh on

       ( rq.form_id = fh.form_id )

       left outer join FormProd.dbo.webuser u1 on

       ( rl.route_to = u1.user_id )

       left outer join FormProd.dbo.webuser u2 on

       ( rl.user_id = u2.user_id )

where ( rq.form_id NOT IN ('SERF','TERM-REQ')) and

       ( rq.date_submit >= getdate() - 10000)

;

Script for Calender:

MinMax:

LOAD

  Min([Create Date]) as MinDate,

  Max([Create Date]) as MaxDate

Resident ARRequestList;

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

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

LET vToday = $(vMaxDate);

TempCal:

LOAD

  Date($(vMinDate) + rowno() - 1) AS TempDate

  AutoGenerate

  $(vMaxDate) - $(vMinDate) +1;

DROP TABLE MinMax;

MasterCalendar:

LOAD

TempDate as [Create Date],

Week(TempDate) as Week,

Year(TempDate) as Year,

Day(TempDate) as Day,

Month(TempDate) as Month,

WeekDay(TempDate) as WeekDay,

'Q' & ceil(month(TempDate)/3) as Quarter,

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

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

InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),-1) * -1 as LastYTDFlag

Resident TempCal

Order By TempDate ASC;

DROP TABLE TempCal;  

2 Replies
petter
Partner - Champion III
Partner - Champion III

Reading your Load Script it seems to me that the ARRequestList and MasterCalendar tables should end up being linked through the key field [Create Date]. Are you saying that they are not linked or are the values in the two tables not matching or do you get an error message when you run your Load Script?

Not applicable
Author

Hi Petter,

Yes, they are not linked together. I do not have an error message, but choosing either the date or one of my values, will not associate with the other field.

Ill post a sample qvw here