I need help with formula tool - How does it works?

[Edu_XGomes]

Hi everyone,

I am working with in-dabase tools but I am lost... I am Alteryx user for a long time but starting for these day with in-database...

I received some SQL instructions to run in a database but I don´t know where I should put them so I thought I should put them in a in-db formula:

I need to get the  Count distinct from two variables so I put it in an in-db formula tool

COUNT(DISTINCT pes, cpfcnpj) AS qtd_cpf_cnpj

Please see the attached.

But it is not running as well. Can you guys give a tip how formula tool works? Or where I should put the SQL instructions?

Thanks a lot!

[apathetichell · Accepted solution]

You can add that directly to your query in the in-db connect tool -  Try it in your query language screen of your in-db connect. For formula tool - fields need to be in ' and I'd insert them using insert fields directly to get syntax correct.

[christine_assaad]

Hi @Edu_XGomes 

Can't you use the Summarize IN DB? It can get you the Count Distinct.

Cheers!

[Edu_XGomes]

Thanks ! but how could I use it with 2 variables?

For instance my database can contain:

USA - NYC

USA - NYC

USA - CHI

FRA - PAR

FRA - PAR

So my expect result would be:

USA - NYC

USA - CHI

FRA - PAR

[christine_assaad]

Hi @Edu_XGomes 

You can use the same field as "Group by" as well as "Count distinct". Is this a middle step in the process or the desired end result?

[apathetichell]

try something like:

COUNT(DISTINCT (CONCAT(pes, cpfcnpj)) AS qtd_cpf_cnpj

basically you can either use formula or sql query to CONCAT (pes, cpfcnpj) and turn that into one value.