Pivot On N8N

Sathya_Narayanan · November 19, 2021, 12:25pm

Hi,

I am working on data formating and i have a list of domain names with me ( which are duplicted as well).

I am trying to do a pivot table using N8N to find the unique domain names with the count.

Is there a way or a node i can use to achive this, just the way we do regular pivot on excell?

Thanks,
Sathya.R

MutedJam · November 19, 2021, 12:31pm

Hi @Sathya_Narayanan, would the Remove Duplicate operation of the Item Lists node be what you’re looking for? This allows removing duplicates. If you need to cross-reference data to identify duplicates, there’s also the Merge node offering a Remove Key Matches operation.

If these aren’t what you’re looking for, could you share an example of the data you are working with?

Sathya_Narayanan · November 19, 2021, 12:34pm

Hi,

Thanks for the response, what i am trying to achive from the data that i have is some thing like this.

Domain Count
a 10
b 15
c 5

It like similar to pivot fucntion which is avaiable on excel

MutedJam · November 19, 2021, 12:38pm

I see, you might find this example helpful then: Get values with no duplicates - #7 by MutedJam

Sathya_Narayanan · November 20, 2021, 8:15am

Hi,

Sorry for the late response, I am very new to java script so your example really helped me a lot in getting the count of domain.

Now with that said, i am also trying to run a API query to a 3rd party URL to find if the domain is disposable or not.

I tired to use the HTTP node to achive it and i get the result, but the problem is the API on respond TRUE/FLASE and it doesnt say about the domain name. Is there a way to achive what i am looking for?

I am using the follwing 3rd party API to validate the domain
Getting Started with Kickbox

Example:

Domain. disposable
gmail. no
yahoo. no
check. yes

I know the above is possible using function node, but again, as mentioned i am new to java and the syntax i tried using google doesnt work on the node.

Jon · November 20, 2021, 8:42am

Hey @Sathya_Narayanan,

You have a couple of options you can use the HTTP Request node to call the api you want to use which looks like it would work well or you could use the One Simple API node which has an email check option that should do what you are after.

Sathya_Narayanan · November 20, 2021, 8:53am

Hi,

I dont find that node on my N8N installation, do i have to add this node? if yes, can you please let me know as to how to do it and more over, i need to validate the domain name alone and not the email address. Does this node support that as well?

Sorry if my questions are lame, new to this and curious to know.

Jon · November 20, 2021, 9:03am

Hey,

It was released in version 149 so you may need to update unless you are using the desktop version.

It works on the entire email address like Kickbox does but the result will tell you if the domain is known to be a disposable address.

Sathya_Narayanan · November 20, 2021, 9:07am

Thanks checked the documetation and found that i can upgrde the n8n version using npm update -g n8n

One quick question, will this upgrade destory my exisitng workflows?, do i have to run any back up before upgrade ?

Please let me know.

Sathya_Narayanan · November 20, 2021, 9:15am

Hi,

I have upgrded it and it seem to be fine, however, the simple one api seems to be a paid services. Even the free one gives me less number of domain api checker.

50 request per hour is less

Jon · November 20, 2021, 9:16am

You should be all good, to be safe you could export all the workflows using the cli tool CLI commands for n8n | Docs

Sathya_Narayanan · November 20, 2021, 9:32am

Hey jon,

Even the one simple api is not helping me because the result i got when it try to validate a domain gives me a response like this.

‘’’ [

{

“is_format_valid”: true,

“is_domain_valid”: false,

“domain_has_valid_mx_records”: true,

“is_email_free”: false,

“is_email_disposable”: false,

“is_email_role”: false,

“canonical”: “”,

“deliverability”: “BAD”,

“safe_to_register_as_user”: false,

“elapsed”: 0.12239813804626465

}’’’

In the above response i dont know to which domain it has this response, meaning with out the domain name i will not be prepare a report.

But what i am looking for is very simple table like this.

Domain Disposiable
gmail.com no
yahoo.com no
checker.com yes

something similar to this.

Jon · November 20, 2021, 11:03am

Hey @Sathya_Narayanan,

I have popped together a quick worklfow to show how to use OneSimpleAPI and Kickbox through the HTTP Request Node.

The function node just returns 4 emails address that both services will use, Because OneSimpleAPI doesn’t return the domain (Just put in a feature request with the chap that makes it) we use the Merge node to join it up then the set node at the end to just return the Domain and Disposable result.

On the bottom part we use the same email addresses then use the HTTP Request node to call Kickbox then we use the set node to return the 2 columns.

Example Workflow

Hopefully this helps

Jon · November 20, 2021, 2:10pm

Quick update…

The change to OneSimpleAPI has happened so now the response has everything needed, New workflow is below that shows both options.

The API change doesn’t require any changes to n8n it was a backend change and I would like to thank Pablius for being so quick

Example Workflow

Sathya_Narayanan · November 22, 2021, 5:28am

Hey Jon,

Thank you, i got things working… you guys are gr8…