Parsing a column of text file - best practices

By your definitions, the 1st Saturday of a quarter will be on the 1st of a month; on the 31st, 30th, 29th, 28th, 27th, or 26th of the previous month if it has 31 days; or on the 30th, 29th, 28th, 27th, 26th, or 25th of the previous month if it has 30 days. Therefore, the last Saturday of a quarter will be on the 25th, 24th, 23rd, 22nd, 21st, 20th, or 19th of a 31 day month or on the 24th, 23rd, 22nd, 21st, 20th, 19th, or 18th of a 30 day month. So, the code you're using in the GetFirstLastWeek() function might work for certain quarters of certain years, but it can't provide the correct results for the general case.

What are the English definitions of what is to be printed in your output following the text:

1. ALLC_CurrentWeek,
2. ALLC_CurrentPeriod,
3. PriorQuarterAD,

?

You didn't show us the source for the script /home/oracle/Hyperion_Batch/Scripts/Batch/_env.sh . How does it set the values for the variables _YEAR , _MONTH , and _DAY ? Are they the calendar date on which the script is run? (If so, many of your calculations will be wrong. If you had run this script on 12/31/2016, your script would be trying to find dates based on FY4Q2016 instead of FY1Q2017 .)

How have you tested this code to verify that the results produced are correct when run in the 1st and last week in a quarter where the calendar month and calendar year match the fiscal year and fiscal month (in one case) and where the calendar and fiscal month and year are different? (You might consider having your script look for a single command-line argument. If it is found, use that argument as the current date instead of using the actual current calendar date. That will allow you to see if you get what you want on any date you want to try past, present, or future. Otherwise, use the current date like your script does now.)

Also, please provide the English definitions of what is to be printed in your output following the text: CurrentQtrInput, and PriorQtrInput, .

Hi Don -

Here are the contents of _env.sh:

#::::::::::::::::::::::::::::
#::-- Set Time Variables --::
#::::::::::::::::::::::::::::

_DAY=$(date +%d)
_MONTH=$(date +%m)
_QUARTER=$(((_MONTH+2)/3))
_YEAR=$(date +%Y)
_DATESTAMP=${_YEAR}${_MONTH}${_DAY}
_HOUR=$(date +%H)
_MINUTE=$(date +%M)
_SECOND=$(date +%S)
_TIME=${_HOUR}${_MINUTE}${_SECOND}
_DATETIMESTAMP=${_DATESTAMP}_${_TIME}

_PREV_DAY=$(date --date yesterday "+%d")
_PREV_MONTH=$(date --date yesterday "+%m")

And yes, you are right, if run during those special occasions, my code would be wrong for quite a few variables. My next task was going to be trying to solve for that.

1. ALLC_CurrentWeek - Day in which script is ran

2.ALLC_CurrentPeriod - Assumes the same behavior of what we spoke about before, your posy #18.

3.PriorQuarterAD - Same concept as post #18.
4.CurrentQtrInput -Same concept as post #18
5.PriorQtrInput - Same concept as post #18.

For instance, if day (Saturday which is the day the script is ran) is 12/31, ALLC_CurrentWeek would be 12/31/16, ALLC_CurrentPeriod would be Jan, and PriorQuarter would be De, CurrentQtrInput would be Jan, and PriorQtrInput would be Dec.

Thank you, Don!

You're giving examples again; not definitions. And the strings associated with these values do not correlate with the values you seem to want following them.

You seem to be saying that ALLC_CurrentWeek is a synonym for CurrentWeek . Why have two names for the same thing? If they aren't the same, what is the difference between them?

Note that I say the names don't correlate to the strings because a day ( 12/31/16 ) is not a week (12/31/16-01/06/17), but the string you put before the output specifying the Saturday at the start of the week in which you invoke this program is named CurrentWeek (and maybe also ALLC_CurrentWeek ). Sometimes you call a month a Period and sometimes you call a month a Mnth and in your examples above you seem to be calling a month a Qtr . Naming something PriorQtrInput and assigning it a value that is an abbreviated month name provides me with no mnemonic relationship.

Please define the above terms in English; not just with examples from which you hope I'll be able to guess at a definition.

Hi Don -

Sorry for the delay - was away on PTO fly-fishing for the last week.

To your comments about certain substitution variables (ALLC_CurrentWeek & CurrentWeek) being synonymous, yes that is correct. Certain target systems required different naming conventions for the subvars, even though each are the same value.

Yes, 12/31/16 is NOT a week, but rather a day. However, this particular client forecasts every week, and therefore, this indicates the start of the current Forecast week.

Mnth and Period are the same thing = Month. Again, the name convention is dictated by which target system they are going to, but they mean the same thing.

PriorQtrInput is the prior quarter (i.e. 1 2 3 or 4)
PriorQtrMnth1= the first month of the previous quarter

1. ALLC_CurrentWeek - The date in MM/DD/YY format that the script is executed. (In the 12/31/16 example, ALLC_CurrentWeek would be 12/31/16)
2. ALLC_CurrentPeriod - The current Month in which the script is executed, unless the next Saturday is greater than or equal to the 2nd day of the month, then ALLC_CurrentPeriod would be that month (In the 12/31/16 example, ALLC_CurrentPeriod would be JAN17.)
3. PriorQuarterAD - Prior quarter number, unless the next Saturday is greater than or equal to the 2nd day of the month AND in the next quarter, then PriorQuarter would actually be the CURRENT quarter (In the 12/31/16 example, PriorQuarterAD would be AD4-16 ...note the "4")
4. CurrentQtrInput - Current quarter number, unless the next Saturday is greater than or equal to the 2nd day of the month AND in the next quarter, then CurrentQtrInput would actually be the next quarter (In the 12/31/16 example, CurrentQtrInput value would be FY 1Q2017_input ...note "1")
5. PriorQtrInput - Same definition as #3 from above (In the 12/31/16 example, PriorQtrInput would be FY 4Q2016_input)

Also, here is the output when I ran it on 5/23/17 (I ran it on a Tuesday, rather than a Saturday, but you get the point):

CurrentWeek,'\05/23/17\'
CurrentWeekq,'\"05/23/17\"'
CurrentPeriod,MAY17
CurrentPeriodq,'\"MAY17\"'
"1PeriodPrior",APR17
"1PeriodPriorq",'\"APR17\"'
"2PeriodPrior",MAR17
"2PeriodPriorq",'\"MAR17\"'
CurrentQuarter,'\FY 2Q2017\'
CurrentQuarterq,'\"FY 2Q2017\"'
CurrentHalfq,'\"FY 1H2017\"'
CurrentPlanYear,'\FY 2017\'
CurrentPlanYearq,'\"FY 2017\"'
CurrentYear,'\FY 2017\'
CurrentYearq,'\"FY 2017\"'
PriorQuarterAD,'\AD1-17\'
PriorQuarterADq,'\"AD1-17\"'
PriorQuarterMnth1,JAN17
PriorQuarterMnth2,FEB17
PriorQuarterMnth3,MAR17
PriorQuarterMnth1q,'\"JAN17\"'
PriorQuarterMnth2q,'\"FEB17\"'
PriorQuarterMnth3q,'\"MAR17\"'
PriorQuarter,'\FY 1Q2017\'
PriorQuarterq,'\"FY 1Q2017\"'
CurrentQtrInput,'\FY 2Q2017_input\'
CurrentQtrInputq,'\"FY 2Q2017_input\"'
PriorQtrInput,'\FY 1Q2017_input\'
ALLC_CurrentWeek,'\05/23/17\'
ALLC_CurrentWeekq,'\"05/23/17\"'
ALLC_CurrentPeriod,MAY17
ALLC_CurrentPeriodq,'\"MAY17\"'
FirstQtrWeek,'\04/01/2017\'
LastQtrWeek,'\06/24/2017\'

Thank you for all your help, Don! Please let me know if you need any additional clarity. Again, the /\"' symbols encasing the values of each subvar are required to be able to import spaces and special characters.

Please show us the actual contents of one of the actual files that you have shipped to one of your customers (preferably for the data you would have gotten by running your script on Saturday, May 28, 2017 so we can more easily verify which month and quarter values are based on Calendar week dates and which are based on Fiscal week dates). After playing around with your problem a bit, it seems that calendar week values are based on the date values for the Saturday in the week and fiscal week values are based on the date values for the Friday at the end of the same week. Note that the code I'm playing with always produces data for a Saturday (the current date if I run it on Saturday or give it a date to process that is a Saturday OR the immediately prior Saturday if I run it on any other day of the week or give it a date to process that is not a Saturday), so showing me values showing results for a Tuesday just confuses things.

The quoting you have shown us in post #25 for some of these variable can't possibly be correct. For example, the quoting on:

CurrentWeek,'\05/23/17\'

with a zero backslash-escaped and one of two single-quotes backslash-escaped makes absolutely no sense to me as a programmer trying to imagine why any parser would want that input. If you have historical data for the files you have produced for your customers, please show us contents of the files you sent out on 05/28/2017 (presumably the last data you sent out), 01/28/2017, and 12/31/2016.

Hi Don -

Thanks for the reply.

I do not have historical content as I'm going through an effort to automate this as it's currently done manually. Then, I'll enable an archive piece (as you see in the script).

The content does not get shipped to customers. What happens is it produces the content output as shown above. Then, the shell script reads col1 and col2 and imports that into the target system as substitution variable and its value.

The \/' symbols are used to import. The particular engine that does the actual import (maxl) requires those symbols as escape characters to import properly.

So, lets just test one, CurrentWeek.

${_SUBVARPATH}${_YEAR}_${_MONTH}${_DAY}/Subvar_List.txt is created with the following content:

Then, the shell script reads col1 and col2, passes those values to a Maxl script as shown here:

#::-- Read file contents into variables and pass to MaxL --::#

while IFS=',' read col1 col2
do

export subvar=$col2

_MAXLLOGFILE=${_INTRAPATH}/${_FN}_${col1}_MAXL.log

. ${_STARTMAXLPATH} ${_MAXLSCRIPTPATH}Update_Subvars.mxl ${_ESSB_USER} ${_ESSB_PSWD} ${_ESSB_SRVR} ${_MAXLLOGFILE} $col1 $subvar

done < ${_SUBVARPATH}${_YEAR}_${_MONTH}${_DAY}/Subvar_List.txt

The MaxL file (${_MAXLSCRIPTPATH}Update_Subvars.mxl) is as follows:

/* Spool MaxL output to log file */

spool on to $4;

echo MaxL script parameters;
echo Passed in Subvar       = $5;
echo Passed in Subvar value = $subvar;

/* login <UserName> <Password> on <Essbase Server> */
login $1 $2 on $3;

alter system drop variable $5;
alter system add variable $5 $subvar;

logout;
spool off;
Exit 0;

In the above code, $5 is the subvar (col1) and $subvar is the value (col2).

The log file is as follows:

---------------------------------------------------------
CS_Subvar_Advancement.sh beginning at 164709
---------------------------------------------------------
---------------------------------------------------------
Prepare Subvars
---------------------------------------------------------
---------------------------------------------------------
Prepare Subvars : Successful
---------------------------------------------------------
---------------------------------------------------------
Advance Subvar Values
---------------------------------------------------------
---------------------------------------------------------
Advance Subvar Values : Successful
---------------------------------------------------------
---------------------------------------------------------
Update Subvars on exalytics-madc-01.server.lan
---------------------------------------------------------
~/Hyperion_Batch/Files/Subvar/2017_0605 ~
~

 Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.4.008B019)
 Copyright (c) 2000, 2016, Oracle and/or its affiliates.
 All rights reserved.

MaxL script parameters

Passed in Subvar       = CurrentWeek

Passed in Subvar value = 06/05/17

MAXL> login admin welcome1 on exalytics-madc-01.server.lan;

 OK/INFO - 1051034 - Logging in user [admin@Native Directory].
 OK/INFO - 1241001 - Logged in to Essbase.

MAXL> alter system drop variable CurrentWeek;


MAXL> alter system add variable CurrentWeek '06/05/17';

 OK/INFO - 1056090 - System altered.

MAXL> logout;

      User admin is logged out


  MaxL Shell completed

---------------------------------------------------------
Update Subvars on exalytics-madc-01.server.lan : Successful
---------------------------------------------------------

---------------------------------------------------------
CS_Subvar_Advancement.sh - Completed Successfully

Normal Exit
---------------------------------------------------------

And then the the import process reflects the subvar and value as follows:

(see attachment).

So again, the \/'" symbols are used as escape characters for the MAXL engine to import correctly with spaces, slashes and double quotes in the value.

Does that make more sense?

Attachment

The use of backslashes in your example:

CurrentWeek,'\06/05/17\'

makes absolutely no sense to me at all. Please humor me and show us the results of running those same steps with each of the following alternative inputs:

CurrentWeek,'06/05/17'
CurrentWeek,"06/05/17"
CurrentWeek,06/05/17
CurrentWeek,'\"06/05/17\"'
CurrentWeek,06\/05\/17
CurrentWeek,06\\/05\\/17
CurrentWeek,06\\\/05\\\/17
CurrentQuarter,'"FY 2Q2017"'
CurrentQuarter,'\"FY 2Q2017\"'
CurrentQuarter,FY\ 2Q2017

Hi Don -

I performed the task as you wished. What I did, in the interst of time, is just append a suffix to each to make them unique so I could import all at one time:

Here is what I added to to ${_SUBVARPATH}${_YEAR}_${_MONTH}${_DAY}/Subvar_List.txt so can be read in for import:

aCurrentWeek,'06/05/17'
bCurrentWeek,"06/05/17"
cCurrentWeek,06/05/17
dCurrentWeek,'\"06/05/17\"'
eCurrentWeek,06\/05\/17
fCurrentWeek,06\\/05\\/17
gCurrentWeek,06\\\/05\\\/17
hCurrentQuarter,'"FY 2Q2017"'
iCurrentQuarter,'\"FY 2Q2017\"'
jCurrentQuarter,FY\ 2Q2017

Here are the only ones that worked:

(see attached)

It does look like some variations worked. HOwever please note, the subvars suffixed with "q" (in my original list) needs to be imported WITH " surrounding the value.

Thank you for all your help, Don.

Also, here are the log files from the ones that failed to import:

OK. Thanks for running the test. It looks like quoting is a lot simpler and saner than you had indicated before.

Thank you, Don. Please let me know if you need anything else. Anxious to see the results.

Thanks!

Hi Don -

Struggling with this one to account for those exceptions. Would this be something you could help me with in regards to the proper code?

Thank you!

Hi SIMMS7400,
I'm still playing with it in the background... running into problems with more undefined variables and it appears that your script depends on being run from a certain directory which is not mentioned in your comments. So:

1. What is the current working directory in which you run your script?
2. How is _MAINPATH defined?
3. How is _LOGPATH defined?
4. How is _ERRORPATH defined?
5. How is _STARTMAXLPATH defined? And, if it isn't defined to be an absolute pathname, in what directory is it located?
6. How is _MAXLSCRIPTPATH defined?
7. In what directory is CS_Subvar_Advancement.sh located?
8. How are _ESSB_USER , _ESSB_PSWD , and _ESSB_SRVR defined?

I wouldn't be surprised if there will be more questions after these are answered, but I am making (slow) progress.

Hi Don -

Thank you for the note! You're in CA responding at 130am - do you ever sleep? LOL

I apologize, I thought I included my environment file but I didn't.

Here it is:

#!/bin/bash
#::--:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
#::-- Script Name: _env.sh                                                    --::
#::--                                                                         --::
#::-- Description: This environment file is set to hold all variables         --::
#::--              for all Hyperion/Oracle scripts requiring:                 --::
#::--                                                                          --::
#::--              1. Path variables                                          --::
#::--              2. User ID(s)                                              --::
#::--              3. Logon(s)                                                --::
#::--              4. Password(s)                                              --::
#::--              5. Server(s)                                                   --::
#::--              6. Application(s)                                          --::
#::--               7. Database(s)                                             --::
#::--               8. etc                                                      --::
#::--                                                                          --::
#::--  Calls:      Not Applicable                                             --::
#::--  Called By:  All Scripts                                                --::
#::--                                                                              --::
#::-- Parameters:  Not Applicable                                               --::
#::--                                                                              --::
#::-- Author:      Name (Company )                                             --::
#::-- Date:                                                                       --::
#::                                                                              --::
#::--:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

#::::::::::::::::::::::::::::
#::-- Set Path Variables --::
#::::::::::::::::::::::::::::

cd $HOME

_MAINPATH=$(pwd)/Hyperion_Batch/
_LOGPATH=Logs/
_ERRORPATH=Errors/
_FILEPATH=Files/
_FLAGFILEPATH=${_MAINPATH}${_FILEPATH}FlagFiles/
_BATCHPATH=${_MAINPATH}Scripts/Batch/
_MAXLSCRIPTPATH=${_MAINPATH}Scripts/MaxL/
_SUBVARPATH=${_MAINPATH}${_FILEPATH}Subvar/

#:::::::::::::::::::::::::::::::
#::-- Set Essbase Variables --::
#:::::::::::::::::::::::::::::::

_ESSB_USER=user
_ESSB_PSWD=password
_ESSB_SRVR=exalytics-madc-01.server.lan

_STARTMAXLPATH=/u02/EssbaseServer/essbaseserver1/bin/startMaxl.sh

#::::::::::::::::::::::::::::
#::-- Set Time Variables --::
#::::::::::::::::::::::::::::

_DAY=$(date +%d)
_MONTH=$(date +%m)
_QUARTER=$(((_MONTH+2)/3))
_YEAR=$(date +%Y)
_DATESTAMP=${_YEAR}${_MONTH}${_DAY}
_HOUR=$(date +%H)
_MINUTE=$(date +%M)
_SECOND=$(date +%S)
_TIME=${_HOUR}${_MINUTE}${_SECOND}
_DATETIMESTAMP=${_DATESTAMP}_${_TIME}

_PREV_DAY=$(date --date yesterday "+%d")
_PREV_MONTH=$(date --date yesterday "+%m")

#:::::::::::::::::::::::::::::::::::
#::-- Export Relevant Variables --::
#:::::::::::::::::::::::::::::::::::

export _MAINPATH _LOGPATH _ERRORPATH  _FILEPATH _FLAGFILEPATH _SCRIPTPATH _MAXLSCRIPTPATH _SUBVARPATH
export _ESSB_USER _ESSB_PSWD _ESSB_SRVR _STARTMAXLPATH
export _DAY _MONTH _QUARTER _YEAR _DATESTAMP _HOUR _MINUTE _SECOND _TIME _DATETIMESTAMP _PREV_DAY _PREV_MONTH

That should answer all your questions above but please let me know if you need anything else. Again thank you so much for helping me!

Sleep. What's that?

The way that _env.sh calculates _QUARTER only works if the quarter you are interested in is based on the calendar month in which you run your script. I thought the quarter used in your script was supposed to be based on the fiscal month (not the calendar month). For example, if you had run your script on Saturday, December 31, 2016 do you want that to be 1Q2017 (based on the fiscal month) or 4Q2016 (based on the calendar month)?

Do any of the other scripts that source _env.sh depend on the way it sets _QUARTER ? The code I'm writing currently waits to set the variables it uses that specify quarters until after it has determined in which fiscal quarter the most recent Saturday before the date on which the script was run (or the date given to it as an operand if an operand was found on the command line) is located.

Hi Don -

If I run my script on Saturday, December 31st, 2016, it should be 1Q2017 - correct.

Also, please do what you need to with the _QUARTER variable. I only made that variable for this process so please build as necessary. Nothing else is dependent on that.

Thank you!

Please just remove the definition of _QUARTER from _env.sh (so no other scripts see it and are lured into using it incorrectly).

My script won't be depending on any of the time variables defined in _env.sh ; it will only be using the path and Essbase variables it sets.

Note that the way you're setting each of your time variables with separate invocations of date can lead to confusing results. For example if you start your script at ~23:59:59 on 12/31/2016 the value assigned to _DATETIMESTAMP could be any of the following:

20161231_235959
20161231_235900
20161231_230000
20161231_000000
20171231_000000
20170131_000000
20170101_000000

assuming that all of the invocations of the date utiity are completed in a period of 1 second (showing results that cover a period of about 30 days).

When getting a group of related time variables, it is safer to do so with one invocation of date . For example:

read _DAY _MONTH _YEAR _HOUR _MINUTE _SECOND <<-EOF
        $(date '+%d %m %Y %H %M %S')
EOF
_DATESTAMP=${_YEAR}${_MONTH}${_DAY}
_TIME=${_HOUR}${_MINUTE}${_SECOND}
_DATETIMESTAMP=${_DATESTAMP}_${_TIME}

which will reduce the possibilities to a consistent date and time stamp with all values at the end of 12/31/2016 or all values at the start of 1/1/2017.

Are we having fun yet?

Hi Don -

This is great - I learn every time you post. It's helped me immensely and I can't thank you enough.

Will do I will remove QUARTER variable going forward. Thank you again and I'm pumped to see the end results!