18 Replies Latest reply: Feb 20, 2012 1:09 PM by Stefan Wühl RSS

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

        • Separating Date Field by Staggered Shift Schedule
          Stefan Wühl

          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

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

              • Re: Separating Date Field by Staggered Shift Schedule

                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

                    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
                        Stefan Wühl

                        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

                            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
                                Stefan Wühl

                                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

                                    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
                                        Stefan Wühl

                                        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

                                            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
                                                Stefan Wühl

                                                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

                                                    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

                                                      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
                                                          Stefan Wühl

                                                          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

                                      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

                                        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
                                            Stefan Wühl

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