Help with script resident join

ivandrago — Wed, 03 Nov 2010 23:50:07 GMT

Hi,

I have the below script, which basically gives a calendar for each personid for the dates between '01-JAN-2009' and previous day; but I need it changing, I want it to only show the date upto when that personid left the company and not sure how to do this, I have had a go under the orginal script but getting errors.

//Original Script

Cal_Tmp1:
Load
PersonId
Resident People;

Join LOAD
date,
date as DateForKey,
week,
month,
year,
[shift 1],
dow as [Day of Week],
[S1 Time];
SQL SELECT
convert(varchar,c.datekey,103) as date,
convert(varchar,right(convert(varchar,c.datekey,103),2))+'.'+case when len(c.weeknum) = 1 Then '0'+convert(varchar,c.weeknum) else convert(varchar,c.weeknum) end as week,
substring(datename(m,c.datekey),1,3)+'-'+substring(datename(yy,c.datekey),3,2) as month,
CalYear as year,
case when c.WeekdayNum between 1 and 5 then 1 else 0 end as [shift 1],
datename(dw,c.datekey) as dow,
case when c.WeekdayNum between 1 and 5 then 7.5 else 0 end as [S1 Time]
From
dimCalendar c
where
c.datekey between '01-JAN-2009' and dateadd(d,-1,getdate()) ;

//New Script

Cal_Tmp1:
Load
PersonId
Resident People;

Join LOAD
date,
date as DateForKey,
week,
month,
year,
[shift 1],
dow as [Day of Week],
[S1 Time];
SQL SELECT
convert(varchar,c.datekey,103) as date,
convert(varchar,right(convert(varchar,c.datekey,103),2))+'.'+case when len(c.weeknum) = 1 Then '0'+convert(varchar,c.weeknum) else convert(varchar,c.weeknum) end as week,
substring(datename(m,c.datekey),1,3)+'-'+substring(datename(yy,c.datekey),3,2) as month,
CalYear as year,
case when c.WeekdayNum between 1 and 5 then 1 else 0 end as [shift 1],
datename(dw,c.datekey) as dow,
case when c.WeekdayNum between 1 and 5 then 7.5 else 0 end as [S1 Time]
From
Calendar c cross join User p left join PersonLogin pl
on p.personid = pl.login left join person pe
on pl.userid = pe.userid

where
c.datekey between '01-JAN-2009' and case when pe.terminationdate is null then dateadd(d,-1,getdate()) else pe.terminationdate end;

Help with script resident join

pover — Thu, 04 Nov 2010 00:15:38 GMT

Have you thought about changing your strategy to include the beginning and end date in the table People and then doing a intervalmatch between the that table and the master calendar table?

Regards.

Help with script resident join

ivandrago — Thu, 04 Nov 2010 09:29:03 GMT

How would I do this?

Help with script resident join

pover — Sat, 06 Nov 2010 22:39:43 GMT

Ivan,

Here is an example. Instead of Start and Stop you would use Start Date and End Date in your example. The time field would then be you date field from your master calendar.

Regards.

topic Help with script resident join in QlikView

Help with script resident join

Help with script resident join

Help with script resident join

Help with script resident join