Help with signal table: Vendor by two or more payment methods

[santiagolc]

Hi, I need some help with a widget I am trying to make in signavio.

I am looking to see vendors with two or more payment methods.

That is, as I show in the following case:

I would like to only see those cases that have two payment methods like the first three, while the fourth case, I would like it not to appear.

If you have any idea how to solve it, it would be of great help.

Thank you!

[nicolasthies1 · Accepted solution]

Hi Santiago,

I did some testing and talked to some of my colleagues to see if we can solve your problem.

The code from the table of your screenshot cannot be modified in a way to filter out the customers with <= 1 payment methods while and displaying the three columns you're showing due to SiGNAL / SQL logic of how sub queries and GROUP BY function.

However, if you're just interested in seeing a table of customers with more than one payment method, this code might work for you:

SELECT
  Proveedor,
  "Count Método de Pago"
FROM
  (
    SELECT
      "PO Vendor Name" AS "Proveedor",
      count(DISTINCT "Payment Method Code REGUH") AS "Count Método de Pago"
    FROM
      THIS_PROCESS
  ) AS SUB
WHERE
  "Count Método de Pago" > 1

[former_member1198702]

Hi Santiago, please let me know if the following code works. I've now filtered for >1 payment type.

SELECT
"Proveedor",
"Count Método de Pago",
"Metodo de pago"

FROM (

SELECT 
"PO Vendor Name" as "Proveedor"
,COUNT("Payment Method Code REGUH") as "Count Método de Pago"
, case 
when "Payment Method Code REGUH" = 'T'
then 'T - Transferencia Bancaria'
when "Payment Method Code REGUH" = 'Z'
then 'Z - Vale Vista (Pago)'
when "Payment Method Code REGUH" = 'C'
then 'C - Cheques (Emitidos)'
END as "Metodo de pago"
FROM THIS_PROCESS) as sub
WHERE "Count Método de Pago" > 1
ORDER BY 2 DESC

[santiagolc]

Hello, unfortunately the solution did not work for us. The payment methods are always more than one, so it will always be greater than one the result of >.

For example for a vendor, you can have 5000 T and 200 C, so it will be > to 1.

I was thinking of maybe finding a way where we can find that if the vendor has at least 1 T and 1 C, then it recognizes that that user has more than 1 payment method. But we haven't found a way to do that yet.

[santiagolc]

Hi Nicolas,

i would share my code for the SIGNAL table:

SELECT 
"PO Vendor Name" as "Proveedor"
,COUNT("Payment Method Code REGUH") as "Count Método de Pago"
, case 
when "Payment Method Code REGUH" = 'T'
then 'T - Transferencia Bancaria'
when "Payment Method Code REGUH" = 'Z'
then 'Z - Vale Vista (Pago)'
when "Payment Method Code REGUH" = 'C'
then 'C - Cheques (Emitidos)'
END as "Metodo de pago"
FROM THIS_PROCESS
ORDER BY 2 DESC

[nicolasthies1]

Hi Santiago,

you would have to create a SiGNAL table for this widget. Could you paste the current SiGNAL code you have with the required column names in here?