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

Separating Date Field by Staggered Shift Schedule

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:

GFScreenshot01 Feb. 01 04.32 PM dnelson.jpg

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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:

//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.

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

View solution in original post

18 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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?

Not applicable
Author

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!

swuehl
MVP
MVP

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

Not applicable
Author

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.

swuehl
MVP
MVP

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?

Not applicable
Author

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.

swuehl
MVP
MVP

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