Solved: Re: Separating Date Field by Staggered Shift Sched... - Page 2 - Qlik Community

Report Inappropriate Content · ‎2012-02-01

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

Report Inappropriate Content · ‎2012-02-07

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?

swuehl · ‎2012-02-07

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?

Report Inappropriate Content · ‎2012-02-07

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.

Report Inappropriate Content · ‎2012-02-12

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?

swuehl · ‎2012-02-12

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

Report Inappropriate Content · ‎2012-02-20

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?

swuehl · ‎2012-02-20

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

Report Inappropriate Content · ‎2012-02-20

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?

swuehl · ‎2012-02-20

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

Separating Date Field by Staggered Shift Schedule