Participating Organization/Organisation Search

[notshi]

From https://iaticonnect.org/topic/participating-organizationorganisation-search

@Michelle-IOM

In attempting to find how a participating organization has been classified by other publishers, I just realized that the data store appears to only allow for searching on IATI publishers in the participating organization field. However the organization I'm looking for and many other participating organizations are not IATI publishers.

I can also see in d-portal that I can search on the organization name in the free-text search bar and find that the organization is listed in many IATI files but I don't see a way to get a dump from d-portal that can help me compare the org type across all those publishers.

In this particular instance I'm looking to see how GIZ is classified by other publishers. (government, public private partnership, CSO) But I have others that I need to look up so I need a method to do this repetitively for other organizations.

Questions:

1) Are there any data users out there with any advice on how to get to this data without going one-by-one into each activity spit out by D-Portal?

2) Are there any publishers out there that are willing to tell me how you have classified GIZ in your files?

3) Can someone from the IATI technical team explain why the data store search is restricted to publishers and is there is another way to get out all participating organizations regardless of publisher status? If there is no solutions I will advocate for a user case here.

[notshi]

Hi @Michelle-IOM,

We are able to use dquery to come up with this query that might be useful - Link You'll need to click on Run Query and wait for the results to load.

Alternatively, here are the results in CSV and JSON

A snippet of what it looks like and what it means:

    role_: "4",
    type_: "70",
    narrative: "DEUTSCHE GESELLSCHAFT FUR INTERNATIONALE ZUSAMMENARBEIT (GIZ) GMBH",
    publisher: "XI-IATI-EC_DEVCO",
    count: "181"

Publisher XI-IATI-EC_DEVCO has GIZ listed in participating-org/narrative and the @role is assigned as 4 and @type is 70. This publisher has listed GIZ 181 times in the participating-org element.

When we use references from the Registry and Standard, this can also be read as:

European Commission - Directorate-General for International Partnerships (DEVCO) has listed GIZ as a participating org with an Implementing role and Private Sector org type.

[Michelle-IOM]

Dear DevInt/D-Portal Team,

I very much appreciate your attempt at help here. Sadly, I am a finance professional and have very little understanding on how to get data out unless it is spoon fed to me. I don’t have internal IT support either to have someone within our organization do it for me. This is why I was really hoping there was a user friendly tool in existence but it seems from all the posts that there isn’t……yet.

Having said that I did click on the link and hit run query (that much I can do) as you instructed however it came back with the below error.

@.***D71BE5.393C41B0]

The same error was returned when I clicked on the CSV link.

Kind Regards, Michelle

Michelle Y. Levesque Senior Project Manager Department of Resources Management (DRM) International Organization for Migration (IOM)http://www.iom.int/ Geneva – Switzerland (GMT +1) Office: +41-(0)22 717 94 83 Mobile: +41 (0)78 647 31 86 Email/Skype: @.**@.>

www.iom.inthttp://www.iom.int/ I Facebookhttps://www.facebook.com/pg/IOM/posts/?ref=page_internal I Twitterhttps://twitter.com/unmigration

@.***D71BE5.393C41B0]

From: shi @.> Sent: 2021-Mar-Wed 11:09 PM To: devinit/D-Portal @.> Cc: LEVESQUE Michelle @.>; Mention @.> Subject: Re: [devinit/D-Portal] Participating Organization/Organisation Search (#613)

Hi @Michelle-IOMhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FMichelle-IOM&data=04%7C01%7Cmylevesque%40iom.int%7Cb307c2c67bf84d6603a608d8e99143d7%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516157411061622%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=X54VwPgohcST2OnUm5LOoCg9vYRMRCjpPwL4FPK%2FTuc%3D&reserved=0,

We are able to use dquery to come up with this query that might be useful - Linkhttps://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fd-portal.org%2Fdquery%2F%23%250A%250Aselect%250A%250Axson-%253E%253E%27%40role%27%2520as%2520role_%2C%2520%250Axson-%253E%253E%27%40type%27%2520as%2520type_%2C%2520%250Axson-%253E%27%2Fnarrative%27-%253E0-%253E%253E%27%27%2520as%2520narrative%2C%2520%250Apid%2520as%2520publisher%2C%2520%250Acount(*)%250A%250Afrom%2520xson%2520where%2520root%3D%27%2Fiati-activities%2Fiati-activity%2Fparticipating-org%27%2520%250Aand%2520xson-%253E%27%2Fnarrative%27-%253E0-%253E%253E%27%27%2520like%2520%27%2525(GIZ)%2525%27%250A%250Agroup%2520by%2520xson-%253E%253E%27%40role%27%2C%2520%250Axson-%253E%253E%27%40type%27%2C%2520%250Apid%2C%2520%250Anarrative%250A%250Aorder%2520by%25205%2520desc%250A%250Alimit%252020000%3B%250A&data=04%7C01%7Cmylevesque%40iom.int%7Cb307c2c67bf84d6603a608d8e99143d7%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516157411071620%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=AwJVn15X%2FBx5IGlf1AU1gH2erLlY0jmD1hfXx5%2BcbMU%3D&reserved=0 You'll need to click on Run Query and wait for the results to load.

Alternatively, here are the results in CSVhttps://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fd-portal.org%2Fdquery%3Fform%3Dcsv%26sql%3Dselect%250A%250Axson-%253E%253E%2527%2540role%2527%2520as%2520role_%252Cxson-%253E%253E%2527%2540type%2527%2520as%2520type_%252Cxson-%253E%2527%252Fnarrative%2527-%253E0-%253E%253E%2527%2527%2520as%2520narrative%252Cpid%2520as%2520publisher%252Ccount(*)%250A%250Afrom%2520xson%2520where%2520root%253D%2527%252Fiati-activities%252Fiati-activity%252Fparticipating-org%2527%2520and%2520xson-%253E%2527%252Fnarrative%2527-%253E0-%253E%253E%2527%2527%2520like%2520%2527%2525(GIZ)%2525%2527%250A%250Agroup%2520by%2520xson-%253E%253E%2527%2540role%2527%252Cxson-%253E%253E%2527%2540type%2527%252Cpid%252Cnarrative%250A%250Aorder%2520by%25205%2520desc%250A%250Alimit%252020000%253B%250A&data=04%7C01%7Cmylevesque%40iom.int%7Cb307c2c67bf84d6603a608d8e99143d7%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516157411081611%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=l8ehIWa9HjdB%2FG%2B%2F97dkeRceT7MXVCLGLS3OIeUfVAk%3D&reserved=0 and JSONhttps://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fd-portal.org%2Fdquery%3Fsql%3Dselect%250A%250Axson-%253E%253E%2527%2540role%2527%2520as%2520role_%252Cxson-%253E%253E%2527%2540type%2527%2520as%2520type_%252Cxson-%253E%2527%252Fnarrative%2527-%253E0-%253E%253E%2527%2527%2520as%2520narrative%252Cpid%2520as%2520publisher%252Ccount(*)%250A%250Afrom%2520xson%2520where%2520root%253D%2527%252Fiati-activities%252Fiati-activity%252Fparticipating-org%2527%2520and%2520xson-%253E%2527%252Fnarrative%2527-%253E0-%253E%253E%2527%2527%2520like%2520%2527%2525(GIZ)%2525%2527%250A%250Agroup%2520by%2520xson-%253E%253E%2527%2540role%2527%252Cxson-%253E%253E%2527%2540type%2527%252Cpid%252Cnarrative%250A%250Aorder%2520by%25205%2520desc%250A%250Alimit%252020000%253B%250A&data=04%7C01%7Cmylevesque%40iom.int%7Cb307c2c67bf84d6603a608d8e99143d7%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516157411081611%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=9LQHhIWjpbqvOijMJWuv0Ua382ct3JJQxqW1CzSricY%3D&reserved=0

A snippet of what it looks like and what it means:

role_: "4",

type_: "70",

narrative: "DEUTSCHE GESELLSCHAFT FUR INTERNATIONALE ZUSAMMENARBEIT (GIZ) GMBH",

publisher: "XI-IATI-EC_DEVCO",

count: "181"

Publisher XI-IATI-EC_DEVCO has GIZ listed in participating-org/narrative and the @role is assigned as 4 and @type is 70. This publisher has listed GIZ 181 times in the participating-org element.

When we use references from the Registryhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fiatiregistry.org%2Fpublisher%3Fq%3DXI-IATI-EC_DEVCO%26sort%3Dtitle%2Basc&data=04%7C01%7Cmylevesque%40iom.int%7Cb307c2c67bf84d6603a608d8e99143d7%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516157411091607%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=x1udKaL3ZUPQZ7Z5QDXU4LdLlq0LAr8sZWy29uwnCKc%3D&reserved=0 and Standardhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fiatistandard.org%2Fen%2Fiati-standard%2F203%2Factivity-standard%2Fiati-activities%2Fiati-activity%2Fparticipating-org%2F&data=04%7C01%7Cmylevesque%40iom.int%7Cb307c2c67bf84d6603a608d8e99143d7%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516157411091607%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=%2BixJPBVG%2FvMOThA5vgrMkTHGlvh5vlEqewOxKwCHurM%3D&reserved=0, this can also be read as:

European Commission - Directorate-General for International Partnerships (DEVCO) has listed GIZ as a participating org with an Implementing role and Private Sector org type.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdevinit%2FD-Portal%2Fissues%2F613%23issuecomment-801474468&data=04%7C01%7Cmylevesque%40iom.int%7Cb307c2c67bf84d6603a608d8e99143d7%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516157411101601%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=4j1t30YlYQuUZRpjRya4bFE5UCDMgoL%2Bm62NhqiJ%2FCc%3D&reserved=0, or unsubscribehttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAJMD4FENGZZ5SSQS6KDRSF3TEER7VANCNFSM4ZLNC5PQ&data=04%7C01%7Cmylevesque%40iom.int%7Cb307c2c67bf84d6603a608d8e99143d7%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516157411101601%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=lv7N1S7koGUXDOSTskEByr3JrASHehhYrxoq2J1brDY%3D&reserved=0.

Caution! This message originated from outside IOM.

================= The information contained in this electronic message and any attachments are intended for specific individuals or entities, and may be confidential, proprietary or privileged. If you are not the intended recipient, please notify the sender immediately, delete this message and do not disclose, distribute or copy it to any third party or otherwise use this message. The content of this message does not necessarily reflect the official position of the International Organization for Migration (IOM) unless specifically stated. Electronic messages are not secure or error free and may contain viruses or may be delayed, and the sender is not liable for any of these occurrences.

[notshi]

Hi @Michelle-IOM,

Thanks for trying. It looks like there might be some browser plugins or similar issues preventing you from accessing the site. However, hopefully by you raising this as a use case, the feature will be funded and developed as a user friendly tool by the data task force or the Secretariat.

Meanwhile, because you were able to specifically list out what you needed, we are able to use our internal tool to create a complex query to get some results.

I've attached an Excel file of the results (Github doesn't allow CSV attachments at the moment). GIZ_dquery.xlsx

Hopefully you can access that.

[Michelle-IOM]

Hi Shi,

This is very helpful but at the risk of overstaying my welcome, I have a couple of clarifying questions about the returned data.

Is the Role and Type in relation to GIZ (column B) or the publisher who reference GIZ (column C)?

Is it possible to provide the org reference used for GIZ if one exists in the publisher’s data set. I see “null” where no type is listed so “null” would be fine if no reference is provided. Oddly enough IOM isn’t in the list of publishers which has any reference to GIZ. I know we have published 12 projects funded by them under the name GIZ - Deutsche Gesellschaft fur Internationale Zusammenarbeit GmbH with a org reference of DE-DAC-5-52.

We use the DAC code because we currently classify GIZ as part of the German government. But as the organization is also a listed company in Germany we could have just as correctly used DE-HRB-12394 which I pieced together using the IATI guidelines and the German registry of companies (HRB). But the whole question is if it should be considered a government. If the type listed in this file is meant to reflect how GIZ has been classified by other publishers, I can see that 10 - government, 15 – other public sector, 40 - multilateral, 70 – private sector and 90 - other have all been used.

Please don’t feel obligated to re-run the query. I appreciate this is all just one request and ultimately I have a series of organizations I need this sort of data pull on. GIZ was just an example as it is a current struggle. But to the extent this is something that is quick and can be replicated easily enough it is greatly appreciated.

Kind Regards, Michelle

Michelle Y. Levesque Senior Project Manager Department of Resources Management (DRM) International Organization for Migration (IOM)http://www.iom.int/ Geneva – Switzerland (GMT +1) Office: +41-(0)22 717 94 83 Mobile: +41 (0)78 647 31 86 Email/Skype: @.**@.>

www.iom.inthttp://www.iom.int/ I Facebookhttps://www.facebook.com/pg/IOM/posts/?ref=page_internal I Twitterhttps://twitter.com/unmigration

@.***D71C08.631D0000]

From: shi @.> Sent: 2021-Mar-Thu 2:02 PM To: devinit/D-Portal @.> Cc: LEVESQUE Michelle @.>; Mention @.> Subject: Re: [devinit/D-Portal] Participating Organization/Organisation Search (#613)

Hi @Michelle-IOMhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FMichelle-IOM&data=04%7C01%7Cmylevesque%40iom.int%7C363c4745076c4a83d44308d8ea0e04ab%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516693941113546%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=qExyoK0OMEjxuk1TyCijKcPQ3EKnIjB7eNYdXHsdbN4%3D&reserved=0,

Thanks for trying. It looks like there might be some browser plugins or similar issues preventing you from accessing the site. However, hopefully by you raising this as a use case, the feature will be funded and developed as a user friendly tool by the data task force or the Secretariat.

Meanwhile, because you were able to specifically list out what you needed, we are able to use our internal tool to create a complex query to get some results.

I've attached an Excel file of the results (Github doesn't allow CSV attachments at the moment). GIZ_dquery.xlsxhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdevinit%2FD-Portal%2Ffiles%2F6164224%2FGIZ_dquery.xlsx&data=04%7C01%7Cmylevesque%40iom.int%7C363c4745076c4a83d44308d8ea0e04ab%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516693941123538%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=31tkckh4RMc%2FvkY21ufNYr2Fdeh4giIeAuoN66Usl6I%3D&reserved=0

Hopefully you can access that.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdevinit%2FD-Portal%2Fissues%2F613%23issuecomment-801908511&data=04%7C01%7Cmylevesque%40iom.int%7C363c4745076c4a83d44308d8ea0e04ab%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516693941123538%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=b1nXtAAsDzy4ybZfm7pVuJg5OK4w6CCL%2FJv5Kx0Ch6M%3D&reserved=0, or unsubscribehttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAJMD4FGMAUKUIDFQXE5UXH3TEH2UNANCNFSM4ZLNC5PQ&data=04%7C01%7Cmylevesque%40iom.int%7C363c4745076c4a83d44308d8ea0e04ab%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516693941133531%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=R27iwMIgc%2BWHUPk3PbBTIdbjM6ql4VqDFjJ7xJXbd4s%3D&reserved=0.

Caution! This message originated from outside IOM.

================= The information contained in this electronic message and any attachments are intended for specific individuals or entities, and may be confidential, proprietary or privileged. If you are not the intended recipient, please notify the sender immediately, delete this message and do not disclose, distribute or copy it to any third party or otherwise use this message. The content of this message does not necessarily reflect the official position of the International Organization for Migration (IOM) unless specifically stated. Electronic messages are not secure or error free and may contain viruses or may be delayed, and the sender is not liable for any of these occurrences.

[notshi]

Hi @Michelle-IOM,

Many thanks for adding in those extra information as it provides more information for a better query.

Is the Role and Type in relation to GIZ (column B) or the publisher who reference GIZ (column C)?

Both role and type is in relation to GIZ. All columns are what the publisher has published in the data about GIZ. This explains why there are many different types of spelling for GIZ as a participating organisation.

I've edited the query to include the @ref so you can see what other publishers are using to reference GIZ. I've also edited the query so that it searches the participating-org/narrative for any combinations of GIZ. You should now be able to see IOM listed with12 counts of GIZ. GIZ_dquery1.xlsx

[Michelle-IOM]

Dear Shi,

This is FANTASTIC and exactly what I was looking for. Now if I could only do what you do for so many other non-publishing participating organizations. Don’t worry I won’t ask you to be my personal data extractor. I so appreciate this already.

Kind Regards, Michelle

Michelle Y. Levesque Senior Project Manager Department of Resources Management (DRM) International Organization for Migration (IOM)http://www.iom.int/ Geneva – Switzerland (GMT +1) Office: +41-(0)22 717 94 83 Mobile: +41 (0)78 647 31 86 Email/Skype: @.**@.>

www.iom.inthttp://www.iom.int/ I Facebookhttps://www.facebook.com/pg/IOM/posts/?ref=page_internal I Twitterhttps://twitter.com/unmigration

@.***D71C1F.9AD34560]

From: shi @.> Sent: 2021-Mar-Thu 4:32 PM To: devinit/D-Portal @.> Cc: LEVESQUE Michelle @.>; Mention @.> Subject: Re: [devinit/D-Portal] Participating Organization/Organisation Search (#613)

Hi @Michelle-IOMhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FMichelle-IOM&data=04%7C01%7Cmylevesque%40iom.int%7Cad7269ae212a4eb25ff308d8ea22ea1d%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516783724845827%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=bXbz2X7hk6A8f%2BechogdVbRGv8I1%2FxhrfucBuc8Ptms%3D&reserved=0,

Many thanks for adding in those extra information as it provides more information for a better query.

Is the Role and Type in relation to GIZ (column B) or the publisher who reference GIZ (column C)?

Both role and type is in relation to GIZ. All columns are what the publisher has published in the data about GIZ. This explains why there are many different types of spelling for GIZ as a participating organisation.

I've edited the query to include the @ref so you can see what other publishers are using to reference GIZ. I've also edited the query so that it searches the participating-org/narrative for any combinations of GIZ. You should now be able to see IOM listed with12 counts of GIZ. GIZ_dquery1.xlsxhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdevinit%2FD-Portal%2Ffiles%2F6165279%2FGIZ_dquery1.xlsx&data=04%7C01%7Cmylevesque%40iom.int%7Cad7269ae212a4eb25ff308d8ea22ea1d%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516783724845827%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=bmr6oUJUfv4aleB%2F%2BrSTs02qjygo93hA9M7cB9cmAA4%3D&reserved=0

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fdevinit%2FD-Portal%2Fissues%2F613%23issuecomment-802023412&data=04%7C01%7Cmylevesque%40iom.int%7Cad7269ae212a4eb25ff308d8ea22ea1d%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516783724855818%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=vZ6NVtySPSh1L93C0ZwlbX50%2BIp6oUbudVURiUU5WPQ%3D&reserved=0, or unsubscribehttps://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAJMD4FAWN2UF5W5EJTVS6E3TEIMFLANCNFSM4ZLNC5PQ&data=04%7C01%7Cmylevesque%40iom.int%7Cad7269ae212a4eb25ff308d8ea22ea1d%7C1588262d23fb43b4bd6ebce49c8e6186%7C1%7C0%7C637516783724855818%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=LcQz5dsrceMROwLyorauMRs%2FQbFh1t6otXEBnEtnSFw%3D&reserved=0.

Caution! This message originated from outside IOM.

================= The information contained in this electronic message and any attachments are intended for specific individuals or entities, and may be confidential, proprietary or privileged. If you are not the intended recipient, please notify the sender immediately, delete this message and do not disclose, distribute or copy it to any third party or otherwise use this message. The content of this message does not necessarily reflect the official position of the International Organization for Migration (IOM) unless specifically stated. Electronic messages are not secure or error free and may contain viruses or may be delayed, and the sender is not liable for any of these occurrences.

[notshi]

Hi @Michelle-IOM

If you can get someone to do the following on a browser that works (preferably Firefox or Chrome without plugins), you can just edit one part of the query to suit your needs.

1. Copy the text below

   
   select
   xson->>'@role' as participating_org_role, 
   xson->>'@type' as participating_org_type, 
   xson->>'@ref' as participating_org_ref, 
   xson->'/narrative'->0->>'' as narrative, 
   pid as publisher, 
   count(*)

from xson where root='/iati-activities/iati-activity/participating-org' and xson->'/narrative'->0->>'' like '%GIZ%'

group by participating_org_role, participating_org_type, participating_org_ref, pid, narrative

order by 6 desc limit 20000;

2. Visit [dQuery](http://d-portal.org/dquery)
3. Click on the editor and Paste the text
4. Under 'Download XSON', click on **Download XSON as csv**

--------------------------------

This is what it should look like if you did steps 1-3
![Screenshot-20210318175507-697x453](https://user-images.githubusercontent.com/1515961/111673721-2a473d00-8813-11eb-8f0a-0067168ad6ad.png)
*This is the Editor part of the page*

If you look at line 10 on the Editor, you can replace GIZ with other participating orgs.
![Screenshot-20210318175733-400x23](https://user-images.githubusercontent.com/1515961/111674012-75615000-8813-11eb-82e7-28d208269257.png)

You need to make sure to keep the % at both ends of the name and there shouldn't be any spaces.
This is also case sensitive so you probably want to use uppercase or capitalise if that is how it is reported in the data.

You can then click on **Download XSON as csv** to download the csv after you make your changes.