Customer identification if there is an invoice amount for at least one of his orders

[stefano_bianche]

Good morning,

I need help in Qlik.

I need to enter an SQL code to get the actual customer identification for a client (YES / NO).

For me, a client has been effective when there is at least one budget in the job orders during all the years.

I, through the query below, I managed to recover the list of actual customers sectioned for their orders.

SELECT Cliente,* FROM(
SELECT
        CASE
WHEN (ISNULL(Importo1Fattura, -1) = -1 AND ISNULL(Importo2Fattura, -1) = -1 AND ISNULL(Importo3Fattura, -1) = -1) THEN 'ClienteNO'
ELSE 'ClienteSI'
    END 'RapportoCliente',
AnaCommesseInter.IdCliente,
AnaClienti.CLRAGSOC as Cliente
FROM AnaCommesseInter
LEFT JOIN AnaClienti ON AnaClienti.IdCliente = AnaCommesseInter.IdCliente) as x

The result of the query is as follows:

I would like to group them by customer (if there is at least one item 'ClienteSI' is effective for me).

Can you give me a hand?

Thank you.

[isingh30]

Try This at the end of the script.

Group by Cliente where RapportoCliente = 'ClienteSI'

Thanks.

[stefano_bianche]

no, in the sql it is not possible to join the GROUP BY and the WHERE.

he result of the stamp I reported above should be:
Cliente:                         RapportoCliente:

+ WATT S.R.L.               ClienteSI
10 DICEMBRE SRL               ClienteNO

1701 SRL                              ClienteSI

Single voice of column Cliente.

[isingh30]

Thanks for your feedback But, in sql we can use both group by & where clause.

It's just the wrong syntax I've mentioned above. Check the below sample -

USE AdventureWorks;

GO

SELECT ProductModelID, AVG(ListPrice) AS 'Average List Price'

FROM Production.Product

WHERE ListPrice > $1000

GROUP BY ProductModelID

ORDER BY ProductModelID ;

GO

Thanks.

[stefano_bianche]

Hi,thanks for your feed but if I use the group by me group it but I always have 2 records for the client + WATT S.R.L.
I need a single record per customer, but I can not insert the condition where RapportoCliente = 'ClienteSI' otherwise the ClienteNO are no longer shown (I also need that information). I think we must put a special condition for the record count but I do not know how.