Help with IPC Co-Classifikation

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

techno
Posts: 4
Joined: Tue Jul 26, 2016 11:29 am

Help with IPC Co-Classifikation

Post by techno » Tue Jul 26, 2016 11:31 am

It is the first time I use PATSTAT for research purposes and I'm not quite sure how to go along. I used an offline version.

I want to do a simple co classification analysis on IPC Codes. Therefore I create a table trough a join (table named IPC2) which include the information of tls209_appln_ipc and another table of 35 technology fields (TechF). All IPC Codes grouped by the 35 Fields.

Now I want to know: How many Patents are co classified by groups? E.g. how many Patents classified as TechF ‘1’ and TechF ‘2’

I hope you understand my problem and someone can helps me.


mkracker
Posts: 114
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: Help with IPC Co-Classifikation

Post by mkracker » Tue Jul 26, 2016 11:58 am

Short answer: Have a look at table TLS230_APPLN_TECHN_FIELD, which has the data about to which degree an application is related to a technical field. This table will already solve half of your problem.

This table is based on the reference table TLS901_TECHN_FIELD_IPC. The Data Catalog gives you more detail.
-------------------------------------------
Martin Kracker / EPO


techno
Posts: 4
Joined: Tue Jul 26, 2016 11:29 am

Re: Help with IPC Co-Classifikation

Post by techno » Wed Jul 27, 2016 2:08 pm

Thanks for your answer. I worked with an old version. Now I tried it with the new version but it doesn’t works. I hope you can help me again.

That’s my query:


select count(distinct t1.APPLN_ID) as Quantity
from TLS230_APPLN_TECHN_FIELD t1
inner join TLS230_APPLN_TECHN_FIELD t2 on t1.APPLN_ID = t2.APPLN_ID
where t1.TECHN_FIELD_NR = '1' and t2.TECHN_FIELD_NR = '2',


mkracker
Posts: 114
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: Help with IPC Co-Classifikation

Post by mkracker » Thu Jul 28, 2016 10:28 pm

IMO your query is fine. Just the comma at the end must be removed. And you do not need to put the attribute values in quotes because TECHN_FIELD_NR is not a string attribute but a numerical attribute.

So why are you not happy with your result?
-------------------------------------------
Martin Kracker / EPO


techno
Posts: 4
Joined: Tue Jul 26, 2016 11:29 am

Re: Help with IPC Co-Classifikation

Post by techno » Wed Aug 24, 2016 2:04 pm

Thanks for your answer!

My request will be one table that has the information for all Co Classification pairs. But I don’t know how this will be work.


Geert Boedt
Posts: 178
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Help with IPC Co-Classifikation

Post by Geert Boedt » Wed Aug 24, 2016 4:33 pm

It is fairly simple, you need to specify in your "WHERE" clause the conditions to be fulfilled.
If I understood correctly, you are looking for applications that have at least technical field 1 (Electrical machinery, apparatus, energy) and technical field 2 (Audio-visual technology)
Here is the query, I restricted it also with a date, otherwise you would have millions of records.
The "distinct" clause is needed because 1 technical field occurs many times in the tls901 table, linked to the various IPC main group symbols. Because the PATSTAT team has pre-created the link between applications and technological fields (tls230), you don't need the tls209 (IPC) table at all.
Here is the code:

Code: Select all

SELECT  distinct tls201_appln.appln_id
      ,appln_auth
      ,appln_nr
      ,appln_kind
      ,appln_filing_date
      ,appln_nr_epodoc
      ,weight
	  ,tls901_techn_field_ipc.techn_field_nr
	  ,techn_sector
	  ,techn_field
  FROM tls201_appln join tls230_appln_techn_field on tls201_appln.appln_id = tls230_appln_techn_field.appln_id
  join tls901_techn_field_ipc on tls230_appln_techn_field.techn_field_nr = tls901_techn_field_ipc.techn_field_nr
  where tls201_appln.appln_id in (select distinct appln_id from tls230_appln_techn_field where techn_field_nr =  1)
  and   tls201_appln.appln_id in (select distinct appln_id from tls230_appln_techn_field where techn_field_nr =  2)
  and appln_filing_date =  '2014-04-01'
  order by tls201_appln.appln_id
If you now want to count the applications for each of the technical fields, then you could use the following query. (Real number or weighted; you could also exclude the other technological fields that enter the count because of other co-classifications.)

Code: Select all

SELECT tls230_appln_techn_field.techn_field_nr,techn_field, count(tls201_appln.appln_id) #applications, sum(weight)
 weighted  FROM tls201_appln join tls230_appln_techn_field on tls201_appln.appln_id = tls230_appln_techn_field.appln_id
  join tls901_techn_field_ipc on tls230_appln_techn_field.techn_field_nr = tls901_techn_field_ipc.techn_field_nr
  where tls201_appln.appln_id in (select distinct appln_id from tls230_appln_techn_field where techn_field_nr =  1)
  and   tls201_appln.appln_id in (select distinct appln_id from tls230_appln_techn_field where techn_field_nr =  2)
  and appln_filing_date =  '2014-04-01'
  group by tls230_appln_techn_field.techn_field_nr,techn_field
  order by sum(weight) desc
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


techno
Posts: 4
Joined: Tue Jul 26, 2016 11:29 am

Re: Help with IPC Co-Classifikation

Post by techno » Thu Aug 25, 2016 10:13 am

Thank you very much for your time and help! The query helps me very much.


Post Reply