[Wactclc-alma] analytics question STRIP OUT OCLC NUMBER

[Thomas, Kirsti]

IIRC, that string came from the Have_MMSID_Get_OCLC_Number  report that Saint-Jean mentioned.  The report was created by Janice Cristopher at University of Connecticut.

There's a big caveat with the report!

The string returns the first OCLC number it runs across in the Network Number field, reading from left to right.

This works fine for most Alma customers, because when the programmers at Ex Libris transferred data from the MARC record into the Network Number field in the Analytics database, they read the data in from the top of the MARC record to the bottom.  Reading in the data from top to bottom puts the "real" OCLC number from MARC 035$a field before any other OCLC numbers (035$s, 7XX, 8XX) in the Network Number field.

For some weird reason, when Ex Libris transferred data from *our* MARC records into the Network Number field, they read in the numbers from bottom to top.  So in all our tables with a Network Number field, the "real" OCLC number from the 035$a appears as the very last OCLC number in the field.  The string from Janice's report doesn't work for us.

What's ultra-infuriating is that when I reported this problem to Ex Libris, I was told that they have "no standard protocol for the order that 035 data is read into the Network Number field" and they weren't going to make one.

What this means is that you can't rely on them always reading 035 data into the Network Number field from top to bottom.  It's possible that next month they'll start reading your data in from bottom to top and then go back to reading the data in from top to bottom a few months later.  So there's no guarantee that the Have_MMSID_Get_OCLC_Number  report will always work to give you the "real" OCLC number. (You can imagine me saying "GRRRR" here.)

There's an idea in the Idea Exchange to have Ex Libris create a separate Analytics field specifically for OCLC numbers in 035$a and 035$z.  I like that idea so much I gave it 2 votes:

https://ideas.exlibrisgroup.com/forums/308173-alma/suggestions/33077617-new-analytics-field-for-oclc-number-in-bibliograph


Kirsti S. Thomas
Library Technical Services Manager
Seattle Colleges
kirsti.thomas at seattlecolleges.edu<mailto:kirsti.thomas at seattlecolleges.edu>
206-934-4106



From: Wactclc-alma [mailto:wactclc-alma-bounces at lists.ctc.edu] On Behalf Of Paula Laine
Sent: Wednesday, October 24, 2018 12:36 PM
To: WACTCLC Alma Discussion <wactclc-alma at lists.ctc.edu>
Subject: Re: [Wactclc-alma] analytics question STRIP OUT OCLC NUMBER

Me neither. I don't know where it came from but it might be Kirsti.
Paula

From: Wactclc-alma <wactclc-alma-bounces at lists.ctc.edu<mailto:wactclc-alma-bounces at lists.ctc.edu>> On Behalf Of Elena Bianco
Sent: Wednesday, October 24, 2018 12:34 PM
To: WACTCLC Alma Discussion <wactclc-alma at lists.ctc.edu<mailto:wactclc-alma at lists.ctc.edu>>
Subject: Re: [Wactclc-alma] analytics question STRIP OUT OCLC NUMBER

Holy cow, Paula! That string is unbelievable and I never would have figured it out in a million years. Thanks very much! Question: Where did you put that in the query?

Elena Bianco, Systems & Technical Services Librarian
Skagit Valley College
2405 E. College Way
Mount Vernon, WA 98273
(360)416-7624

From: Wactclc-alma <wactclc-alma-bounces at lists.ctc.edu<mailto:wactclc-alma-bounces at lists.ctc.edu>> On Behalf Of Paula Laine
Sent: Wednesday, October 24, 2018 12:16 PM
To: WACTCLC Alma Discussion <wactclc-alma at lists.ctc.edu<mailto:wactclc-alma at lists.ctc.edu>>
Subject: Re: [Wactclc-alma] analytics question STRIP OUT OCLC NUMBER

Elena,

Here is a snippet of code that was posted to the list that strips out the OCLC number from the "network number" field.
It looks awful but it works.

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' END

The rest of it looks like this: [super wide cut&paste]
[cid:image001.png at 01D474FE.2F66D2D0]
Feel free to call me if you'd like.

Paula Laine
425-564-2259

From: Wactclc-alma <wactclc-alma-bounces at lists.ctc.edu<mailto:wactclc-alma-bounces at lists.ctc.edu>> On Behalf Of Elena Bianco
Sent: Wednesday, October 24, 2018 11:56 AM
To: WACTCLC Alma Discussion <wactclc-alma at lists.ctc.edu<mailto:wactclc-alma at lists.ctc.edu>>
Subject: [Wactclc-alma] analytics question

I am trying to run a list of all of the things that we have deleted from Alma in the past year, and I'm realizing that the OCLC numbers are not showing up. We need that so we can do a batch delete in OCLC. I thought that the field Bibliographic ID field would be the one to choose, but this field is blank for a lot of them.

How does one do a search in Analytics and specify that the 035 field be one of the fields that displays - or can you not do that in Analytics? In Millennium, we used to be able to specify which MARC fields to display in any lists that we ran...

Thanks,

Elena

Elena Bianco, Systems & Technical Services Librarian
Skagit Valley College
2405 E. College Way
Mount Vernon, WA 98273
(360)416-7624

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ctc.edu/pipermail/wactclc-alma_lists.ctc.edu/attachments/20181105/c7b73dd3/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 23832 bytes
Desc: image001.png
URL: <http://lists.ctc.edu/pipermail/wactclc-alma_lists.ctc.edu/attachments/20181105/c7b73dd3/attachment.png>