Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data that is in the form of a letter and a number to represent a schedule :
R, N, E, P and 5, 7 that can be combined in any way. (N5, P7, E5, N7, etc.)
They represent Reduced, Normal, Extended and Permanent times (so E is 9am-5pm, N is 7am-7pm, E is 7am-10pm and P is midnight to midnight) ; and the number is how many days a week (so 5 is Monday-Friday, 7 is 7 days a week).
I load an excel file with this information in a column, and I need to calculate how many minutes that is every week - it is a simple calculation ; here is some pseudo-code :
X = 60;
if (SubStringCount("Schedule", 'R'), X*=8,
if (SubStringCount("Schedule", 'N'), X*=12,
if (SubStringCount("Schedule", 'E'), X*=15,
if (SubStringCount("Schedule", 'P'), X*=24);
if (SubStringCount("Schedule", '5'), X*=5,
if (SubStringCount("Schedule", '7'), X*=7);
load X as "Weekly time";
X = 60;
I can't find where to insert this though, if inside the load I get rows in my tables for every If and for the X reset ; if I do it outside the load how do I access the "Schedule" info ?
Any helping hand appreciated 🙂
Hi,
You should be able to extract R,N,E,P and 5,7 and calculate total minutes in a single statement in a LOAD command. For example:
Data:
LOAD
Schedule,
Pick(Match(Left(Schedule,1),'R','N','E','P'),8,12,15,24) * Num(Right(Schedule,1)) * 60 as MinutesPerWeek
FROM [lib://Data/Data.xlsx]
(ooxml, embedded labels, table is Schedule);
Outcome of the script:
Hope this helps,
BR,
Vu Nguyen
This looks exactly like what I want, thank you !