Creating a Resident Load list for a SQL Query

Fri, 24 Jul 2015 21:56:03 GMT

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;

Re: Creating a Resident Load list for a SQL Query

petter — Sat, 25 Jul 2015 01:46:34 GMT

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?

Re: Creating a Resident Load list for a SQL Query

Mon, 27 Jul 2015 16:11:15 GMT

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

topic Re: Creating a Resident Load list for a SQL Query in QlikView

Creating a Resident Load list for a SQL Query

Re: Creating a Resident Load list for a SQL Query

Re: Creating a Resident Load list for a SQL Query