topic Re: Separating Date Field by Staggered Shift Schedule in QlikView

Separating Date Field by Staggered Shift Schedule

Wed, 01 Feb 2012 21:58:11 GMT

In the database I'm working on, I have a date field (update_date) in default date format (M/D/YYYY H:MM:SS etc) and I've been tasked with aligning it with a staggered, compressed 4 shift schedule:

Each color is a different shift (A, B, C, D). Due to the 3-4-3-4 pattern, I thought it would be easier just to use Excel to quickly generate a table of all the dates each shift worked from 2011-2025. So I have all that data, but am stumped as to how to proceed from here. I've tried the following inline tables:

LOAD * INLINE [

update_date, ShiftADates

2/20/2011, Shift A

2/21/2011, Shift A

2/22/2011, Shift A

2/27/2011, Shift A

2/28/2011, Shift A

3/1/2011, Shift A

3/2/2011, Shift A

3/6/2011, Shift A

3/7/2011, Shift A

3/8/2011, Shift A

3/13/2011, Shift A

];

LOAD * INLINE [

ShiftADates, ShiftBDates, ShiftCDates, ShiftDDates

2/20/2011, 2/20/2011, 2/23/2011, 2/23/2011

2/21/2011, 2/21/2011, 2/24/2011, 2/24/2011

2/22/2011, 2/22/2011, 2/25/2011, 2/25/2011

2/27/2011, 2/26/2011, 2/26/2011, 3/2/2011

2/28/2011, 2/27/2011, 3/3/2011, 3/3/2011

3/1/2011, 2/28/2011, 3/4/2011, 3/4/2011

3/2/2011, 3/1/2011, 3/5/2011, 3/5/2011

3/6/2011, 3/6/2011, 3/9/2011, 3/9/2011

3/7/2011, 3/7/2011, 3/10/2011, 3/10/2011

3/8/2011, 3/8/2011, 3/11/2011, 3/11/2011

3/13/2011, 3/12/2011, 3/12/2011, 3/16/2011

];

But I still don't know how to relate the excel generated dates to the update_date field. To further explain what needs to be done, I need to be able to get all the results related to dates in the update_date field that pertain to a certain shift. Perhaps there's a searchbox/bookmark combo that I can use? I've played around with a master calendar but still don't know how to relate/link/associate any of the fields to update_date.

(I'll be checking this until this evening, but I won't be able to respond until Sunday morning. Thanks for all the assistance.)

Separating Date Field by Staggered Shift Schedule

swuehl — Wed, 01 Feb 2012 22:50:52 GMT

Not sure if I understand what you want to achieve with the update_date, since in the first table, you seem to already assign a label (Shift A) to maybe filter your update_date values?

If you load your second table (I think this is the one you create by excel) like this:

SHIFTS:

CROSSTABLE (Shifts, update_date) LOAD recno(), * INLINE [

ShiftADates, ShiftBDates, ShiftCDates, ShiftDDates

2/20/2011, 2/20/2011, 2/23/2011, 2/23/2011

2/21/2011, 2/21/2011, 2/24/2011, 2/24/2011

2/22/2011, 2/22/2011, 2/25/2011, 2/25/2011

2/27/2011, 2/26/2011, 2/26/2011, 3/2/2011

2/28/2011, 2/27/2011, 3/3/2011, 3/3/2011

3/1/2011, 2/28/2011, 3/4/2011, 3/4/2011

3/2/2011, 3/1/2011, 3/5/2011, 3/5/2011

3/6/2011, 3/6/2011, 3/9/2011, 3/9/2011

3/7/2011, 3/7/2011, 3/10/2011, 3/10/2011

3/8/2011, 3/8/2011, 3/11/2011, 3/11/2011

3/13/2011, 3/12/2011, 3/12/2011, 3/16/2011

];

Your crosstable will be transformed into a straight table with two field, update_date (this will link to your other table) and Shifts, that label your dates accordingly. So you can easily select the update_dates that a certain shift has been working.

Maybe this gives you an idea, if this is not helping you, maybe you could elaborate a bit more what you need to do with the data.

Regards,

Stefan

Separating Date Field by Staggered Shift Schedule

Mon, 06 Feb 2012 12:50:30 GMT

Thank you. I will try your suggestion and if needed I can certainly elaborate on this.

Re: Separating Date Field by Staggered Shift Schedule

Mon, 06 Feb 2012 14:20:08 GMT

That is partially what I needed, thank you. What seems to be happening with that code is that it's merely adding records to to the exisiting update_date field such as "2/20/2011 12:00:00 AM". I was hoping that I could give Qlikview a list of dates and somehow link those dates to the existing update_date field. So when I click "ShiftADates" in the inline "Shifts" field, it would narrow down the possible dates to the "ShiftADates".

Also, I still need some way to indicate the times, too (6:00 AM-6:30 PM and so on as shown in the above picture). I'm now running into the problem that the only times pulled are 12:00:00 AM. Should I add...

 h:mm:ss[.fff]

...to the end of all the dates or something similar? It'll be a snap to do if needed, don't worry about that. Or maybe a search box/bookmark approach?

Re: Separating Date Field by Staggered Shift Schedule

Mon, 06 Feb 2012 18:42:12 GMT

I'm reaching the end of my sanity with this thing. To clarify, this is my predicament:

I have a date field I'm loading from a database, update_date.
- This field is in the following format: 6/1/2011 12:33:44 PM
I have every date each shift does and does not work up to 2025 in D/M/YYYY format on an excel sheet and can rearrange as needed
I need to filter the update_date field by Shift on demand (button/bookmark/etc)
I cannot figure out how to associate the date values in update_date with any inline data

Help!

Re: Separating Date Field by Staggered Shift Schedule

swuehl — Mon, 06 Feb 2012 23:28:48 GMT

Ok,

I understood that you want to match some Timestamps to intervals (these intervals are defined by your Shifts). So when picking any Timestamp (update_date), you want to see in which Shift that happens, and for each Shift, you want to see any associated Timestamps, right?

One way to do this would be to create a StartTime and a EndTime for each Shift, for each date. You could do this in Excel and import the three fields StartTime, EndTime, Shift or create them in the load script.

Then you could use an IntervalMatch load prefix to create a link between these Start and End times and your update_date timestamps.

Please look into the Help, IntervalMatch, for more info.

Since your StartTime and EndTime follow a strict rule / pattern, I decided to choose another approach. You could just load the update_date field from your DB and then check for the assocoiated Shift by some Date / Time comparision. Maybe like this:

Let vShiftCalendarStart = '2/20/2011';

// Create some demo update_date Timestamps

DEMO:

LOAD

TimeStamp($(#vShiftCalendarStart)+RAND()*30) as update_date1

autogenerate 100;

//Create the Association to Shifts

RESULT:

LOAD *,

if(ShiftA,'ShiftA ','')&if(ShiftB,'ShiftB ','')&if(ShiftC,'ShiftC ','')&if(ShiftD,'ShiftD','') as WhichShifts;

LOAD

update_date1,

if(match(Mod(DayStart(update_date1,)-$(#vCalendarStart),14),'0','1','2','7','8','9','10')

and (frac(update_date1) <= maketime(18,30)) and (frac(update_date1) >= maketime(6)) ,1,0) as ShiftA, //3-4-4-3 daytime schedule

if(match(Mod(daystart(DayStart(update_date1,0,maketime(18)))-$(#vCalendarStart),14),'0','1','2','6','7','8','9')

and ((frac(update_date1) <= maketime(6,30)) or (frac(update_date1) >= maketime(18))),1,0) as ShiftB, //3-3-4-4 nighttime schedule

if(match(Mod(DayStart(update_date1,)-$(#vCalendarStart),14),'3','4','5','6','11','12','13')

and (frac(update_date1) <= maketime(18,30)) and (frac(update_date1) >= maketime(6)) ,1,0) as ShiftC, //4-4-3-3 daytime shedule

if(match(Mod(daystart(DayStart(update_date1,0,maketime(18)))-$(#vCalendarStart),14),'3','4','5','10','11','12','13')

and ((frac(update_date1) <= maketime(6,30)) or (frac(update_date1) >= maketime(18))),1,0) as ShiftD //3-4-4-3 nighttime schedule

Resident DEMO;

Here, I first created four flag field to check for each update_date Timestamp if it is located within the shift (match the day, taking date crossing into account and compare times), then I concatenated the results into one field (since your shifts overlap, update_date could be associated to more then one Shift).

Please see attached.

Regards,

Stefan

Re: Separating Date Field by Staggered Shift Schedule

Tue, 07 Feb 2012 19:38:31 GMT

The QVD you provided worked like a charm when I plugged in all the values from my excel file; but for some reason I'm getting a "script failed to load" error when I try plugging it into my actual QVD. Is there anything in your code I need to take out to use it?

I noticed your script doesn't have the "SET MonthNames" or the "SET DayNames", my QVD does. Not sure if that would do anything though. Maybe the "DEMO" terms? I have no idea why it's not loading.

Separating Date Field by Staggered Shift Schedule

swuehl — Tue, 07 Feb 2012 19:48:13 GMT

Can you post your script? I don't think the missing MonthNames and DayNames will do any harm, at least there shouldn't be a script error. At worst, wrong format settings may lead to non-matching values or wrongly parsed input values.

Could you also post some lines of your input format together with you script?

Re: Separating Date Field by Staggered Shift Schedule

Tue, 07 Feb 2012 20:46:12 GMT

Here's the code on my QVD:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=********;Initial Catalog=request_report;Data Source=fc8sqlpcl;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=*******;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is MOmJ8jhOJpoiuhiUh);
SQL SELECT *
FROM "Request_t".dbo."V_t" 
where
(app_id=3 or app_id=22 or app_id=63)
and
(status_id != 0 and status_id != 11)
and
(status_name != 'Not Feasible' and status_name != 'Closed - No t Required');

LOAD * INLINE [
    app_id, app_name
    3, FSR
    22, RTE
    63, UAR
];

SHIFTS:
CROSSTABLE (Shifts, update_date) LOAD recno(), * INLINE [
    ShiftADates, ShiftBDates, ShiftCDates, ShiftDDates
2/20/2011,2/20/2011,2/23/2011,2/23/2011
2/21/2011,2/21/2011,2/24/2011,2/24/2011
2/22/2011,2/22/2011,2/25/2011,2/25/2011
2/27/2011,2/26/2011,2/26/2011,3/2/2011
2/28/2011,2/27/2011,3/3/2011,3/3/2011
3/1/2011,2/28/2011,3/4/2011,3/4/2011
3/2/2011,3/1/2011,3/5/2011,3/5/2011
3/6/2011,3/6/2011,3/9/2011,3/9/2011
3/7/2011,3/7/2011,3/10/2011,3/10/2011
3/8/2011,3/8/2011,3/11/2011,3/11/2011
3/13/2011,3/12/2011,3/12/2011,3/16/2011
3/14/2011,3/13/2011,3/17/2011,3/17/2011
3/15/2011,3/14/2011,3/18/2011,3/18/2011
3/16/2011,3/15/2011,3/19/2011,3/19/2011
3/20/2011,3/20/2011,3/23/2011,3/23/2011
3/21/2011,3/21/2011,3/24/2011,3/24/2011
3/22/2011,3/22/2011,3/25/2011,3/25/2011
3/27/2011,3/26/2011,3/26/2011,3/30/2011
3/28/2011,3/27/2011,3/31/2011,3/31/2011
3/29/2011,3/28/2011,4/1/2011,4/1/2011
3/30/2011,3/29/2011,4/2/2011,4/2/2011
4/3/2011,4/3/2011,4/6/2011,4/6/2011
];
Let vShiftCalendarStart = '2/20/2011';
DEMO:
LOAD 
TimeStamp($(#vShiftCalendarStart)+RAND()*30) as update_date1
autogenerate 100;
PRESULT:
LOAD *, 
if(ShiftA,'ShiftA ','')&if(ShiftB,'ShiftB ','')&if(ShiftC,'ShiftC ','')&if(ShiftD,'ShiftD','') as WhichShifts;
LOAD  
update_date1,
if(match(Mod(DayStart(update_date1,)-$(#vCalendarStart),14),'0','1','2','7','8','9','10') 
and (frac(update_date1) <= maketime(18,30)) and (frac(update_date1) >= maketime(6)) ,1,0) as ShiftA, //3-4-4-3 daytime schedule
if(match(Mod(daystart(DayStart(update_date1,0,maketime(18)))-$(#vCalendarStart),14),'0','1','2','6','7','8','9') 
and ((frac(update_date1) <= maketime(6,30))  or (frac(update_date1) >= maketime(18))),1,0) as ShiftB, //3-3-4-4 nighttime schedule
if(match(Mod(DayStart(update_date1,)-$(#vCalendarStart),14),'3','4','5','6','11','12','13') 
and (frac(update_date1) <= maketime(18,30)) and (frac(update_date1) >= maketime(6)) ,1,0) as ShiftC, //4-4-3-3 daytime shedule
if(match(Mod(daystart(DayStart(update_date1,0,maketime(18)))-$(#vCalendarStart),14),'3','4','5','10','11','12','13') 
and ((frac(update_date1) <= maketime(6,30)) or (frac(update_date1) >= maketime(18))),1,0) as ShiftD  //3-4-4-3 nighttime schedule
Resident DEMO;

For brevity's sake I clipped a bunch of the inline csv lines, they go up to 1/1/2026.

Separating Date Field by Staggered Shift Schedule

swuehl — Tue, 07 Feb 2012 21:07:28 GMT

Hm, the inline csv lines are to create the shift calendar?

Maybe my previously attached script was a bit confusing in that point, I believe you don't really need the inline table at all. I forgot to delete the table from my script, but essentially all you need is posted in the text of my yesterday's posting.

I created a variable vShiftCalendarStart which should be the starting point for your shift calendar (since the shifts are repeating, it will be an mostly arbitrary date).

I've chosen '2/20/2011' and for example, Shift A is on duty on day shift on days '0','1','2','7','8','9','10' relative to that day, i.e. 2/20, 2/21, 2/22, 2/27...

The Demo table probably needs to be replaced by your table load, my Demo table just creates some update_date data. Ah, I used update_date1 as field name, so you probably need to rename my update_date1 to update_date in the script.

I can't see anything wrong with the script at first glance, so if you still get a script error, could you post the error description? It might also worth while stepping through the script in debug mode (from script editor).

Re: Separating Date Field by Staggered Shift Schedule

Tue, 07 Feb 2012 22:01:15 GMT

I can do away with the inline table no problem as long as some script works in place of it. The error that keeps popping up when I try to reload is "Execution of script failed. Reload old data?", even in the debugger. I've tried a variety of modifications to your post for my purposes, two are below that I've tried. Maybe you could paste the script needed after the "app_id" entry while assuming I'll be loading update_date from the server? Thanks for your time and patience.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';
//connect statement
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=0wdjh0;Initial Catalog=request_report;Data Source=fc8sqlpcl;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=wdawdawd;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is MCMePZVMBDZMWQNOULRB);
//Loading only FSRs, RTEs and UARs; filtering out Cancelled, Draft, Closed - No CR Required and Not Feasible.
SQL SELECT *
FROM "Request_report".dbo."V_MSR" 
where
(app_id=3 or app_id=22 or app_id=63)
and
(status_id != 0 and status_id != 11)
and
(status_name != 'Not Feasible' and status_name != 'Closed - No CR Required');
LOAD * INLINE [
    app_id, app_name
    3, FSR
    22, RTE
    63, UAR
];
Let vShiftCalendarStart = '2/20/2011';
PRESULT:
LOAD *, 
if(ShiftA,'ShiftA ','')&if(ShiftB,'ShiftB ','')&if(ShiftC,'ShiftC ','')&if(ShiftD,'ShiftD','') as WhichShifts;
LOAD  
update_date1,
if(match(Mod(DayStart(update_date1,)-$(#vCalendarStart),14),'0','1','2','7','8','9','10') 
and (frac(update_date1) <= maketime(18,30)) and (frac(update_date1) >= maketime(6)) ,1,0) as ShiftA, //3-4-4-3 daytime schedule
if(match(Mod(daystart(DayStart(update_date1,0,maketime(18)))-$(#vCalendarStart),14),'0','1','2','6','7','8','9') 
and ((frac(update_date1) <= maketime(6,30))  or (frac(update_date1) >= maketime(18))),1,0) as ShiftB, //3-3-4-4 nighttime schedule
if(match(Mod(DayStart(update_date1,)-$(#vCalendarStart),14),'3','4','5','6','11','12','13') 
and (frac(update_date1) <= maketime(18,30)) and (frac(update_date1) >= maketime(6)) ,1,0) as ShiftC, //4-4-3-3 daytime shedule
if(match(Mod(daystart(DayStart(update_date1,0,maketime(18)))-$(#vCalendarStart),14),'3','4','5','10','11','12','13') 
and ((frac(update_date1) <= maketime(6,30)) or (frac(update_date1) >= maketime(18))),1,0) as ShiftD  //3-4-4-3 nighttime schedule
Resident DEMO;

then

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';
//connect statement
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sefsfsef;Initial Catalog=request_report;Data Source=fsfsef;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=efsefs;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is MCMePZVMBDZMWQNOULRB);
//Loading only FSRs, RTEs and UARs; filtering out Cancelled, Draft, Closed - No CR Required and Not Feasible.
SQL SELECT *
FROM "Request_report".dbo."V_MSR" 
where
(app_id=3 or app_id=22 or app_id=63)
and
(status_id != 0 and status_id != 11)
and
(status_name != 'Not Feasible' and status_name != 'Closed - No CR Required');
LOAD * INLINE [
    app_id, app_name
    3, FSR
    22, RTE
    63, UAR
];

Let vShiftCalendarStart = '2/20/2011';
PRESULT:
LOAD *, 
if(ShiftA,'ShiftA ','')&if(ShiftB,'ShiftB ','')&if(ShiftC,'ShiftC ','')&if(ShiftD,'ShiftD','') as WhichShifts;
LOAD  
update_date,
if(match(Mod(DayStart(update_date,)-$(#vCalendarStart),14),'0','1','2','7','8','9','10') 
and (frac(update_date) <= maketime(18,30)) and (frac(update_date) >= maketime(6)) ,1,0) as ShiftA, //3-4-4-3 daytime schedule
if(match(Mod(daystart(DayStart(update_date,0,maketime(18)))-$(#vCalendarStart),14),'0','1','2','6','7','8','9') 
and ((frac(update_date) <= maketime(6,30))  or (frac(update_date) >= maketime(18))),1,0) as ShiftB, //3-3-4-4 nighttime schedule
if(match(Mod(DayStart(update_date,)-$(#vCalendarStart),14),'3','4','5','6','11','12','13') 
and (frac(update_date) <= maketime(18,30)) and (frac(update_date) >= maketime(6)) ,1,0) as ShiftC, //4-4-3-3 daytime shedule
if(match(Mod(daystart(DayStart(update_date,0,maketime(18)))-$(#vCalendarStart),14),'3','4','5','10','11','12','13') 
and ((frac(update_date) <= maketime(6,30)) or (frac(update_date) >= maketime(18))),1,0) as ShiftD  //3-4-4-3 nighttime schedule
Resident DEMO;

Maybe you could paste the script needed after the "app_id" entry while assuming I'll be loading update_date from the server?

Re: Separating Date Field by Staggered Shift Schedule

swuehl — Tue, 07 Feb 2012 23:11:38 GMT

Have you tried commenting my part out, just loading your data? You do a Select *, I would recommend to only load the fields needed and state them in your select, thus I can also see what you are actually loading 😉

Then you need to label your tables and use your table name in the resident load instead of DEMO.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

//connect statement

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sefsfsef;Initial Catalog=request_report;Data Source=fsfsef;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=efsefs;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is MCMePZVMBDZMWQNOULRB);

//Loading only FSRs, RTEs and UARs; filtering out Cancelled, Draft, Closed - No CR Required and Not Feasible.

//Label your Table

Input:

SQL SELECT *

FROM "Request_report".dbo."V_MSR"

where

(app_id=3 or app_id=22 or app_id=63)

and

(status_id != 0 and status_id != 11)

and

(status_name != 'Not Feasible' and status_name != 'Closed - No CR Required');

AppNameTable:

LOAD * INLINE [

app_id, app_name

3, FSR

22, RTE

63, UAR

];

Let vShiftCalendarStart = '2/20/2011';

RESULT:

LOAD *,

if(ShiftA,'ShiftA ','')&if(ShiftB,'ShiftB ','')&if(ShiftC,'ShiftC ','')&if(ShiftD,'ShiftD','') as WhichShifts;

LOAD

update_date,

app_id,

status_id,

status_name,

if(match(Mod(DayStart(update_date,)-$(#vCalendarStart),14),'0','1','2','7','8','9','10')

and (frac(update_date) <= maketime(18,30)) and (frac(update_date) >= maketime(6)) ,1,0) as ShiftA, //3-4-4-3 daytime schedule

if(match(Mod(daystart(DayStart(update_date,0,maketime(18)))-$(#vCalendarStart),14),'0','1','2','6','7','8','9')

and ((frac(update_date) <= maketime(6,30)) or (frac(update_date) >= maketime(18))),1,0) as ShiftB, //3-3-4-4 nighttime schedule

if(match(Mod(DayStart(update_date,)-$(#vCalendarStart),14),'3','4','5','6','11','12','13')

and (frac(update_date) <= maketime(18,30)) and (frac(update_date) >= maketime(6)) ,1,0) as ShiftC, //4-4-3-3 daytime shedule

if(match(Mod(daystart(DayStart(update_date,0,maketime(18)))-$(#vCalendarStart),14),'3','4','5','10','11','12','13')

and ((frac(update_date) <= maketime(6,30)) or (frac(update_date) >= maketime(18))),1,0) as ShiftD //3-4-4-3 nighttime schedule

Resident Input;

Any better success?

Re: Separating Date Field by Staggered Shift Schedule

Tue, 07 Feb 2012 23:21:21 GMT

I will try that Sunday when I get back to the grindstone. Thank you for all your time, I'll let you know how I fare next week.

Re: Separating Date Field by Staggered Shift Schedule

Sun, 12 Feb 2012 21:08:23 GMT

I still get the same "Expression failed to load. Reload old data?" error:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';
4:07 PM 2/12/2012
//connect statement
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=userID;Initial Catalog=request_report;Data Source=fc8sqlpcl;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LFCNOTD1032MNC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is MCMePZVMBDZMWQNOULRB);

//Loading only FSRs, RTEs and UARs; filtering out Cancelled, Draft, Closed - No CR Required and Not Feasible.
Input:
SQL SELECT *
FROM "Request_report".dbo."V_MSR"
where
(app_id=3 or app_id=22 or app_id=63)
and
(status_id != 0 and status_id != 11)
and
(status_name != 'Not Feasible' and status_name != 'Closed - No CR Required');

AppNameTable:
LOAD * INLINE [
    app_id, app_name
    3, FSR
    22, RTE
    63, UAR
];

Let vShiftCalendarStart = '2/20/2011';

RESULT:
LOAD *,
if(ShiftA,'ShiftA ','')&if(ShiftB,'ShiftB ','')&if(ShiftC,'ShiftC ','')&if(ShiftD,'ShiftD','') as WhichShifts;
LOAD
update_date,
app_id,
status_id,
status_name,
if(match(Mod(DayStart(update_date,)-$(#vCalendarStart),14),'0','1','2','7','8','9','10')
and (frac(update_date) <= maketime(18,30)) and (frac(update_date) >= maketime(6)) ,1,0) as ShiftA, //3-4-4-3 daytime schedule
if(match(Mod(daystart(DayStart(update_date,0,maketime(18)))-$(#vCalendarStart),14),'0','1','2','6','7','8','9')
and ((frac(update_date) <= maketime(6,30)) or (frac(update_date) >= maketime(18))),1,0) as ShiftB, //3-3-4-4 nighttime schedule
if(match(Mod(DayStart(update_date,)-$(#vCalendarStart),14),'3','4','5','6','11','12','13')
and (frac(update_date) <= maketime(18,30)) and (frac(update_date) >= maketime(6)) ,1,0) as ShiftC, //4-4-3-3 daytime shedule
if(match(Mod(daystart(DayStart(update_date,0,maketime(18)))-$(#vCalendarStart),14),'3','4','5','10','11','12','13')
and ((frac(update_date) <= maketime(6,30)) or (frac(update_date) >= maketime(18))),1,0) as ShiftD //3-4-4-3 nighttime schedule
Resident Input;

Am I still missing something?

Separating Date Field by Staggered Shift Schedule

swuehl — Sun, 12 Feb 2012 23:06:40 GMT

I found some possible issues:

In your posted script, there is a line which I think shouldn't be there (just after the first ten SET statements):

4:07 PM 2/12/2012

But I think the main problem is that I defined a variable

Let vShiftCalendarStart = '2/20/2011';

but later on used

#vCalendarStart

in several statements of the RESULT load. This is giving a script error almost for sure. You need to use a single, same name in defining and calling of the variable (it was my mistake, since I renamed once the name in the LET statement, but forget to remove the already defined variable and change all occurences to the same name).

If this does not help, please enable logging in the document properties and post the log file of the script execution.

Regards,

Stefan

Re: Separating Date Field by Staggered Shift Schedule

Mon, 20 Feb 2012 15:36:59 GMT

Of course by the time I got to try it again, the criterias changed. The field I need to compare against is add_date, not update_date. I used your suggestions and changed the script so it looks like this after the SET statements:

//connect statementOLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=username;Initial Catalog=request_report;Data Source=source;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=workstation;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is MCMePZVMBDZMWQNOULRB);

//Loading only FSRs, RTEs and UARs; filtering out Cancelled, Draft, Closed - No CR Required and Not Feasible.SQL SELECT update_date as "history_update_date",
    status_name as "history_status_name",
    "request_id",
    "app_id"
FROM "Request_report".dbo."V_REQUEST_HISTORY"
where
(app_id=3 or app_id=22 or app_id=63);

//Loading only FSRs, RTEs and UARs; filtering out Cancelled, Draft, Closed - No CR Required and Not Feasible.SQL SELECT *
FROM "Request_report".dbo."V_MSR"
where
(app_id=3 or app_id=22 or app_id=63)
and
(status_id != 0 and status_id != 11)
and
(status_name != 'Not Feasible' and status_name != 'Closed - No CR Required');

//Mapping existing app_id table to contextual namesLOAD * INLINE [
    app_id, app_name
    3, FSR
    22, RTE
    63, UAR
];

Let vShiftCalendarStart = '2/20/2011';

RESULT:LOAD *,

if(ShiftA,'ShiftA ','')&if(ShiftB,'ShiftB ','')&if(ShiftC,'ShiftC ','')&if(ShiftD,'ShiftD','') as WhichShifts;

LOAD add_date, if(match(Mod(DayStart(add_date,)-

$(#vShiftCalendarStart),14),'0','1','2','7','8','9','10') and (frac(add_date) <= maketime(18,30)) and (frac(add_date) >= maketime(6)) ,1,0) as ShiftA, //3-4-4-3 daytime scheduleif(match(Mod(daystart(DayStart(add_date,0,maketime(18)))-

$(#vShiftCalendarStart),14),'0','1','2','6','7','8','9') and ((frac(add_date) <= maketime(6,30)) or (frac(add_date) >= maketime(18))),1,0) as ShiftB, //3-3-4-4 nighttime scheduleif(match(Mod(DayStart(add_date,)-

$(#vShiftCalendarStart),14),'3','4','5','6','11','12','13') and (frac(add_date) <= maketime(18,30)) and (frac(add_date) >= maketime(6)) ,1,0) as ShiftC, //4-4-3-3 daytime sheduleif(match(Mod(daystart(DayStart(add_date,0,maketime(18)))-

$(#vShiftCalendarStart),14),'3','4','5','10','11','12','13') and ((frac(add_date) <= maketime(6,30)) or (frac(add_date) >= maketime(18))),1,0) as ShiftD //3-4-4-3 nighttime schedule

Resident Input;

But I'm getting a "Table not found" script error. Any ideas?

Re: Separating Date Field by Staggered Shift Schedule

swuehl — Mon, 20 Feb 2012 16:04:37 GMT

You need to label your tables (that's a good practice in general). So if you reference an "Input" table in your last resident load, use

Input:

in front of the load statement that generates the table containing add_date (well, I can't see this field loaded in your first load, but you probably load it somewhere?).

Re: Separating Date Field by Staggered Shift Schedule

Mon, 20 Feb 2012 18:01:10 GMT

The table which has the add_date field is V_MSR. I used asterisks since there's a LOT of fields in each table. Would you have a sample load script that would work?

Re: Separating Date Field by Staggered Shift Schedule

swuehl — Mon, 20 Feb 2012 18:06:49 GMT

Well, just have a look at my example scripts i posted above?

As said, it's important that you label all tables to be able to reference them later on, e.g. in a resident load.

So use a

Input:

label in front of your V_MSR load.

edit:

or do a preceding load instead of the resident:

//Loading only FSRs, RTEs and UARs; filtering out Cancelled, Draft, Closed - No CR Required and Not Feasible.

SQL SELECT update_date as "history_update_date",
    status_name as "history_status_name",
    "request_id",
    "app_id"
FROM "Request_report".dbo."V_REQUEST_HISTORY"
where
(app_id=3 or app_id=22 or app_id=63);

//Loading only FSRs, RTEs and UARs; filtering out Cancelled, Draft, Closed - No CR Required and Not Feasible.

Let vShiftCalendarStart = '2/20/2011';

RESULT:

LOAD *,

if(ShiftA,'ShiftA ','')&if(ShiftB,'ShiftB ','')&if(ShiftC,'ShiftC ','')&if(ShiftD,'ShiftD','') as WhichShifts;

LOAD add_date, if(match(Mod(DayStart(add_date,)-

$(#vShiftCalendarStart),14),'0','1','2','7','8','9','10') and(frac(add_date) <= maketime(18,30)) and (frac(add_date) >= maketime(6)) ,1,0) as ShiftA, //3-4-4-3 daytime scheduleif(match(Mod(daystart(DayStart(add_date,0,maketime(18)))-

$(#vShiftCalendarStart),14),'0','1','2','6','7','8','9') and((frac(add_date) <= maketime(6,30)) or (frac(add_date) >=maketime(18))),1,0) as ShiftB, //3-3-4-4 nighttime scheduleif(match(Mod(DayStart(add_date,)-

$(#vShiftCalendarStart),14),'3','4','5','6','11','12','13') and(frac(add_date) <= maketime(18,30)) and (frac(add_date) >= maketime(6)) ,1,0) as ShiftC, //4-4-3-3 daytime sheduleif(match(Mod(daystart(DayStart(add_date,0,maketime(18)))-

$(#vShiftCalendarStart),14),'3','4','5','10','11','12','13') and((frac(add_date) <= maketime(6,30)) or (frac(add_date) >=maketime(18))),1,0) as ShiftD //3-4-4-3 nighttime schedule

;

SQL SELECT *
FROM "Request_report".dbo."V_MSR"
where
(app_id=3 or app_id=22 or app_id=63)
and
(status_id != 0 and status_id != 11)
and
(status_name != 'Not Feasible' and status_name != 'Closed - No CR Required');

//Mapping existing app_id table to contextual namesLOAD * INLINE [
    app_id, app_name
    3, FSR
    22, RTE
    63, UAR
];