ABCDEF
1
DescriptionSubject AreaCode / PathDocumentationNotesSource
2
Alternate table row shadingAllResults tab > Select top right pencil icon > Select Table View Properties > Check Row styling: Enable alternate stylingAnalytics - How to make the alternate rows of a table be shaded in an Alma Analytics table
3
"-1" resultAllREPLACE("Bibliographic Details"."MMS Id" , '-1', 'None')Analytics displays null values as "-1." Swaps in "None" for more understandable reporting.ALMA-L: Nazzareno Bedini Feb. 21st, 2020
4
Barcode length checkPhysical ItemsLENGTH("Physical Item Details"."Barcode")
Use 2 barcode columns. Select the dropdown in the first column > Edit formula > Select
> String > Length. Filter on the 1st column > 14 OR < 14 		Use for any dimension with a prescribed number of characters by changing the filter values. e.g. Users Subject Area: barcode or Primary ID.Brian Moore
5
Extract OCLC# from Network Number(Bibliographic Details Shared Dimension)Evaluate('regexp_substr(%1,''[0-9]+'', 1,1)',REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('(OCoLC)o',"Bibliographic Details"."Network Number")),LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('(OCoLC)o',"Bibliographic Details"."Network Number")))),';',''))Works on any concatenated field e.g., ISBN, ISSN
6
Extract hour from transaction timeAllSUBSTRING("Loan Date"."Loan Time" FROM 0 FOR 2)
Edit formula > string > substring From 0 for 2 > chng heading to “Loan Hour”		Time format = hh:mm:ss To extract minutes - FROM 3 FOR 2Megan Drake
7
Convert text number to an integerUserse.g. To extract migrated TOT CHECKOUT (Millennium/Sierra) circ activity from the Note field for use in calculations
CAST(SUBSTRING("Note"."Note" FROM 12 FOR 5) AS int)		Use SUBSTRING to remove the "TOT CHKOUT: " from the Note field, CAST to convert the remaining characters into an integer.Brian Moore
8
Extract portion of a text stringAll● SUBSTRING("dimension"."field" FROM n FOR n)
e.g. To shorten the title Laws related to Securities Commission, exchanges and
holdings companies to the first 45 characters
SUBSTRING("Bibliographic Details"."Title" FROM 1 FOR 45)
● SUBSTRING("dimension"."field" FROM LOCATE('string to locate', "dimension"."field")+n FOR n)
e.g. To extract the 8 character date from the Statistics Note field No. Loans: 003 | LastDateReturn: 20090915
SUBSTRING("Physical Item Details"."Statistics Note 2" FROM LOCATE('LastDateReturn: ',"Physical Item Details"."Statistics Note 2")+16 FOR 8)		Rogers, Mike (2016) Tips & Tricks for Alma Analytics. In: ELUNA 2016 Annual Meeting, May 1-6, 2016, Oklahoma City, OK USA. ELUNA 2016 presentation
9
WildcardsAlle.g., Title is LIKE (pattern match) %�%
Permanent Call Number is LIKE (pattern match) BF205_ .%		Useful for 'LIKE' and 'NOT LIKE' pattern matching filters.
% = multiple characters
_ = single character		Brian Moore
10
Find corrupt diacritics in Bibliographic recordsTitles (or Bibliographic Details Shared Dimension)Filter on:
Lifecycle is equal to / is in In Repository
AND
Title is LIKE (pattern match) %&#x%
OR Uniform Title is LIKE (pattern match) %&#x%
OR Author is LIKE (pattern match) %&#x%
OR Edition is LIKE (pattern match) %&#x%
OR Series is LIKE (pattern match) %&#x%
OR Publisher is LIKE (pattern match) %&#x%
OR Publication Place is LIKE (pattern match) %&#x%
OR Title is LIKE (pattern match) %�%
OR Uniform Title is LIKE (pattern match) %�%
OR Author is LIKE (pattern match) %�%
OR Edition is LIKE (pattern match) %�%
OR Series is LIKE (pattern match) %�%
OR Publisher is LIKE (pattern match) %�%
OR Publication Place is LIKE (pattern match) %�%		Be sure to include leading and trailing wildcards (%) in the filters!Brian Moore
11
Rolling time queries (e.g., last 3 mos., transaction/status date > 1 mo. etc.)AllTIMESTAMPADD (SQL_TSI_DAY, -90, CURRENT_DATE)
TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)
Filter > Add More Options > SQL Expression		Oracle BIEE Calendar Date/Time FunctionsExL introduced pre-defined relative date filters in certain date measures in 2019.
12
Funds Overview formulaeFunds Expenditure● Fund Allocation: IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ALLOCATION')), 0)
● Cash Balance: IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ALLOCATION')), 0) - IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'EXPENDITURE')), 0)
● Funds Available: IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ALLOCATION')), 0) – (IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'EXPENDITURE')), 0) + IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ENCUMBRANCE')), 0))
● Percent Remaining (Based on Expenditures): ((IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ALLOCATION')), 0) – IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'EXPENDITURE')), 0)) / IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ALLOCATION')), 0)) * 100
● Percent Remaining (Based on Expenditures & Encumbrances): ((IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ALLOCATION')), 0) - (IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'EXPENDITURE')), 0) + IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ENCUMBRANCE')), 0))) / IFNULL(FILTER("Fund Transactions"."Transaction Amount" USING ("Fund Transaction Details"."Transaction Item Type" = 'ALLOCATION')), 0)) * 100		 Rogers, Mike (2019) Off-the-Grid Reporting: A Few Handy Reports in Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA. ELUNA 2019 presentation
13
Calculate Fund Percentages
Concept: ((Encumbrance + Expenditure) / Allocation) * 100		Funds ExpenditureFormula: (IFNULL(FILTER("Fund Transactions"."TransactionAmount" USING ("Fund Transaction Details"."TransactionItem Type" = 'ENCUMBRANCE')), 0) + IFNULL(FILTER("Fund Transactions"."TransactionAmount" USING ("Fund Transaction Details"."TransactionItem Type" = 'EXPENDITURE')), 0)) / IFNULL(FILTER("Fund Transactions"."TransactionAmount" USING ("Fund Transaction Details"."TransactionItem Type" = 'EXPENDITURE')), 0)) / IFNULL(FILTER("Fund Transactions"."TransactionAmount" USING ("Fund Transaction Details"."TransactionItem Type" = 'ALLOCATION')), 0) * 100Jones, Julene L. and Willemsen, Mary Ellen (2019) Carpe Data with Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA.ELUNA 2019 presentation
14
Pulling specific numbers from a fieldPhysical ItemsCASE WHEN "Physical Item Details"."Statistics Note 2" IS NOT NULL THEN CAST(SUBSTRING("Physical Item Details"."Statistics Note 2" FROM 12 FOR 3) as INT) ELSE CAST('0' as INT) END Rogers, Mike (2019) Off-the-Grid Reporting: A Few Handy Reports in Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA. Basically, we are telling Analytics, “If the field isn’t null, start at the 12th character and extract the next three characters as an integer; if it is null, then cast ‘0’ as an integer”ELUNA 2019 presentation
15
Adding loans from your old ILS with your current total loans (old ILS stats in Statistics Note 2 in this case)Physical ItemsCASE WHEN "Physical Item Details"."Statistics Note 2" IS NOT NULL THEN CAST(SUBSTRING("Physical Item Details"."Statistics Note 2" FROM 12 FOR 3) as INT) ELSE CAST('0' as INT) END + "Physical Item Details"."Num of Loans" Rogers, Mike (2019) Off-the-Grid Reporting: A Few Handy Reports in Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA. ELUNA 2019 presentation
16
Remove leading or trailing data from a text stringAllTRIM(LEADING 'trim_text' FROM "dimension"."field")
TRIM(TRAILING 'trim_text' FROM "dimension"."field")
e.g. Remove the trailing slash from the title field Handbook of environmental sociology /
TRIM(TRAILING '/' FROM "Bibliographic Details"."Title")		Rogers, Mike (2016) Tips & Tricks for Alma Analytics. In: ELUNA 2016 Annual Meeting, May 1-6, 2016, Oklahoma City, OK USA. Note the use of single quotes.ELUNA 2016 presentation
17
Pulling OCLC number from Network Number field(Bibliographic Details Shared Dimension)TRIM(TRAILING '.' FROM TRIM(TRAILING ';' FROM TRIM(TRAILING ' ' FROM TRIM(TRAILING '(' FROM SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('(OCoLC)', "Bibliographic Details"."Network Number") FOR 16))))) Rogers, Mike (2019) Off-the-Grid Reporting: A Few Handy Reports in Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA. ELUNA 2019 presentation
18
Pulling OCLC number from Network Number field (Example using CASE and SQL)(Bibliographic Details Shared Dimension)CASE WHEN UPPER("Bibliographic Details"."Network Number") LIKE '%OCOLC%' THEN REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('(OCOLC',UPPER("Bibliographic Details"."Network Number"))),LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('(OCOLC',UPPER("Bibliographic Details"."Network Number"))))),';','') WHEN UPPER("Bibliographic Details"."Network Number") LIKE '%OCM%' THEN REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('OCM',UPPER("Bibliographic Details"."Network Number"))), LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('OCM',UPPER("Bibliographic Details"."Network Number"))))),';','') WHEN UPPER("Bibliographic Details"."Network Number") LIKE '%OCN%' THEN REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('OCN',UPPER("Bibliographic Details"."Network Number"))), LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('OCN',UPPER("Bibliographic Details"."Network Number"))))),';','') WHEN UPPER("Bibliographic Details"."Network Number") LIKE '%ON%' THEN REPLACE(LEFT(SUBSTRING("Bibliographic Details"."Network Number" FROM LOCATE('ON',UPPER("Bibliographic Details"."Network Number"))), LOCATE(' ', SUBSTRING(CONCAT("Bibliographic Details"."Network Number",'; ') FROM LOCATE('ON',UPPER("Bibliographic Details"."Network Number"))))),';','') ELSE 'No OCLC Number Available' ENDCooper, Alexander and Hamlett, Lisa (2017) Alma, Analytics, and API all the things: Producing custom automated features in a SAAS environment. In: ELUNA Developers Day 2017, May 9, 2017, Schaumburg, Illinois. ELUNA 2017 presentation
19
Pulling OCLC number from Network Number field (Example using CASE and Regular Expressions)(Bibliographic Details Shared Dimension)CASE WHEN EVALUATE('regexp_substr(%1,''OCOLC'')',(UPPER("Bibliographic Details"."Network Number"))) IS NOT NULL THEN EVALUATE('regexp_substr(%1,''OCOLC+[0-9()]+'')',(UPPER("Bibliographic Details"."Network Number")))WHEN EVALUATE('regexp_substr(%1,''OCN'')',(UPPER("Bibliographic Details"."Network Number"))) IS NOT NULL THEN EVALUATE('regexp_substr(%1,''OCN+[0-9]+'')',(UPPER("Bibliographic Details"."Network Number"))) WHEN EVALUATE('regexp_substr(%1,''OCM'')',(UPPER("Bibliographic Details"."Network Number"))) IS NOT NULL THEN EVALUATE('regexp_substr(%1,''OCM+[0-9]+'')',(UPPER("Bibliographic Details"."Network Number")))
WHEN EVALUATE('regexp_substr(%1,''ON'')',(UPPER("Bibliographic Details"."Network Number"))) IS NOT NULL THEN EVALUATE('regexp_substr(%1,''ON+[0-9]+'')',(UPPER("Bibliographic Details"."Network Number")))
ELSE 'No OCLC Number Available' END
Hamlett, Lisa (2019) "And You Thought You Were Nerdy!": Fun With Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA. ELUNA 2019 presentation
20
Date Filter Formulas (Relative Year, Month, Week, Day)All● 3 years in the future: TIMESTAMPADD(SQL_TSI_YEAR, 3, CURRENT_DATE)
● 4 months ago: TIMESTAMPADD(SQL_TSI_MONTH, -4, CURRENT_DATE)
● 5 weeks ago: TIMESTAMPADD(SQL_TSI_WEEK, -5, CURRENT_DATE)
● 6 days in the future: TIMESTAMPADD(SQL_TSI_DAY, 6, CURRENT_DATE)		Jones, Julene L. and Willemsen, Mary Ellen (2019) Carpe Data with Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA.ELUNA 2019 presentation
21
Date Filter Formulas (Relative Year, Month, Week, Day)Fulfillment● All loans in the last 14 days: "Loan Date"."Loan Date" >= TIMESTAMPADD(SQL_TSI_DAY, -14, CURRENT_DATE)
● All loans in the last 2 years: "Loan Date"."Loan Date" >= TIMESTAMPADD(SQL_TSI_YEAR, -2, CURRENT_DATE)
● All loans in the last 3 months: "Loan Date"."Loan Date" >= TIMESTAMPADD(SQL_TSI_MONTH, -3, CURRENT_DATE)
● All loans in the last week: "Loan Date"."Loan Date" >= TIMESTAMPADD(SQL_TSI_WEEK, -1, CURRENT_DATE)		Wolfe, Margaret Briand (2018) Alma Analytics – Tips & Tricks. In: Ex Libris Northeast Users Group Conference, October 11-12, 2018, SUNY University of Albany. ELUNA 2018 presentation
22
Date Filter Formulas (Relative Day of Month/Year)All● First day of last month: TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
● Last day of last month: TIMESTAMPADD( SQL_TSI_DAY, -(1), TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
● First day of current calendar year: TIMESTAMPADD(SQL_TSI_YEAR, 0, TIMESTAMPADD(SQL_TSI_DAY, DAYOFYEAR(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))		Hamlett, Lisa (2019) "And You Thought You Were Nerdy!": Fun With Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA. ELUNA 2019 presentation
23
Year to Date (YTD)FulfillmentSelect Filter from the Loan Date column > Convert this filter to SQL > Enter "Loan Date"."Loan Date" >= TIMESTAMPADD(SQL_TSI_YEAR, 0, TIMESTAMPADD(SQL_TSI_DAY, DAYOFYEAR(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))Excellent example of a recursive formula.Natasha Allen
24
Year to Date (YTD)FulfillmentSelect Loan Year from the Loan Date dimension and filter to the current year.Laura Krier
25
852 formula to get only the i and h subfieldsPhysical ItemsCONCAT(REPLACE(Evaluate('regexp_substr(%1,''\$\$h[^\$]+'', 1,1)',"Holding Details"."852 MARC"), '$$h ', ''), REPLACE(Evaluate('regexp_substr(%1,''\$\$i[^\$]+'', 1,1)',"Holding Details"."852 MARC"), '$$i', ''))Jones, Julene L. and Willemsen, Mary Ellen (2019) Carpe Data with Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA.ELUNA 2019 presentation
26
Date filter on Usage Data for last 5 weeks of dataUsage DataFILTER("Usage Data Details"."JR1 -Journal Usage Counter (total)" USING ("Usage Date"."UsageDate" >= TIMESTAMPADD(SQL_TSI_WEEK, -5, CURRENT_DATE)))Jones, Julene L. and Willemsen, Mary Ellen (2019) Carpe Data with Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA.ELUNA 2019 presentation
27
Date Filter on last year's worth of circulationsFulfillmentFILTER("Loan"."Loans(Not In House)" USING ("Fulfillment"."LoanDate"."LoanDate" >= TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)))Jones, Julene L. and Willemsen, Mary Ellen (2019) Carpe Data with Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA.ELUNA 2019 presentation
28
Parsing Call Numbers using Regular Expressions(Bibliographic Details Shared Dimension)● Extract subject letter: Evaluate('regexp_substr(%1,''[A-z]+'')',"Holding Details"."Permanent Call Number")
● Extract subject number: Evaluate('regexp_substr(%1,''[0-9]+'')',Evaluate('regexp_substr(%1,''[A-z]+[0-9]+'')',"Holding Details"."Permanent Call Number"))
● Extract subject date: Evaluate('regexp_substr(%1,''([A-z])([0-9]+)\ ([0-9]+)'')',"Holding Details"."Permanent Call Number")
● Split call numbers based on a period followed by a letter.
Split 1: REPLACE(Evaluate('regexp_substr(%1,''[^\.]+[^A-z]+'',1,1)',"Holding Details"."Permanent Call Number"),' .',‘’)
Split 2: REPLACE(Evaluate('regexp_substr(%1,''[^\.]+[^A-z]+'',1,2)',"Holding Details"."Permanent Call Number"),' .',‘’)		Hamlett, Lisa (2019) "And You Thought You Were Nerdy!": Fun With Alma Analytics. In: ELUNA 2019 Annual Meeting, April 29-May 3, 2019, Atlanta, GA. ELUNA 2019 presentation
29
Replace or remove textAllREPLACE ("dimension"."field",'some text','something else')
e.g. To remove subfields from call number $$hPN1005.B5$$iH45 2006
REPLACE(REPLACE("Loan Details"."Call Number",'$$h',''),'$$i','')		Rogers, Mike (2016) Tips & Tricks for Alma Analytics. In: ELUNA 2016 Annual Meeting, May 1-6, 2016, Oklahoma City, OK USA. ELUNA 2016 presentation
30
Set up an Alma Analytics report that will give you details of all the items attached to an MMSID using barcode prompt.Use Selection Steps (which should be at the bottom of your Results screen).
Find your MMS ID column in the Steps pane, and add a step with Apply a Condition.
The Condition Type will be “All X values where Y = values”. Make the Action Keep only of MMS ID.
Under Column, select the Barcode field.
You do have to enter a default value, but you can also select Override with Prompt. Then set up your prompt for the Barcode field, and you should get the results you want.		[ALMA-L] Analytics - Use barcode prompt find all items attached to the associated MMSID (question answered by Lachlan Young, UNSW, Australia)ALMA-L login required to view original.ALMA-L
31
Combine text stringsAllUse concatenation (||) ("dimension"."field"||"dimension"."field")
e.g. To combine last name & first name into a single column
"User Details"."Last Name"||', '||"User Details"."First Name"		Rogers, Mike (2016) Tips & Tricks for Alma Analytics. In: ELUNA 2016 Annual Meeting, May 1-6, 2016, Oklahoma City, OK USA. Note the use of single quotes to separate the data fields with comma and space characters.ELUNA 2016 presentation
32
Identity unique or intersecting values in a queryAllUse MINUS / UNION
MINUS queries ...
• Subtract the results of one query from another
• Require that the columns selected in both queries must be identical
UNION queries ...
• Combine the results of two separate queries into one results set
• Require that the columns selected in both queries must be identical
• Exclude duplicate rows (although, a UNION ALL query does include duplicate rows)		Rogers, Mike (2016) Tips & Tricks for Alma Analytics. In: ELUNA 2016 Annual Meeting, May 1-6, 2016, Oklahoma City, OK USA. An excellent explanation is demonstrated at the end of the presentation.ELUNA 2016 presentation
33
Retrieve data using a barcode prompt / Barcode query for all copiesAllUse Selection Steps (bottom of Results screen).
Find MMS ID column in the Steps pane, add a step with Apply a Condition.
Condition Type = “All X values where Y = values”. Action Keep only: MMS ID.
Under Column, select the Barcode field.
You must enter a default value, but you can also select Override with Prompt. Then set up your prompt for the Barcode field, and you should get the results you want.		 How to - An example of using Selection Steps in Alma Analytics to gather all loans for a title by one barcodeALMA-L
34
Setup weekly bins for Circ stats using CASE functionFulfillmentFulfillment SA > Loan Date: Loan Week Number > Edit Formula > Bins tabSpring 2020 bins for circulationChristina Hennessey
35
Substitute "human readable" campus name for Institution CodeAllCASE "Institution"."Institution Code"
WHEN '01CALS_UBA' THEN 'Bakersfield'
WHEN '01CALS_UCI' THEN 'Channel Islands'
WHEN '01CALS_CHI' THEN 'Chico'
WHEN '01CALS_UDH' THEN 'Dominguez Hills'
WHEN '01CALS_UHL' THEN 'East Bay'
WHEN '01CALS_UFR' THEN 'Fresno'
WHEN '01CALS_FUL' THEN 'Fullerton'
WHEN '01CALS_HUL' THEN 'Humboldt'
WHEN '01CALS_ULB' THEN 'Long Beach'
WHEN '01CALS_ULA' THEN 'Los Angeles'
WHEN '01CALS_MAL' THEN 'Maritime'
WHEN '01CALS_UMB' THEN 'Monterey Bay'
WHEN '01CALS_MLM' THEN 'Moss Landing'
WHEN '01CALS_UNO' THEN 'Northridge'
WHEN '01CALS_PUP' THEN 'Pomona'
WHEN '01CALS_USB' THEN 'San Bernardino'
WHEN '01CALS_SDL' THEN 'San Diego'
WHEN '01CALS_SFR' THEN 'San Francisco'
WHEN '01CALS_SJO' THEN 'San Jose'
WHEN '01CALS_PSU' THEN 'San Luis Obispo'
WHEN '01CALS_SOL' THEN 'Sonoma'
WHEN '01CALS_UST' THEN 'Stanislaus'
WHEN '01CALS_USL' THEN 'Sacramento'
WHEN '01CALS_USM' THEN 'San Marcos'
ELSE 'Other'
END
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100