Math and logic in load

[Saryk]

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 🙂

[vunguyenq89]

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

[Saryk]

This looks exactly like what I want, thank you !