Dynamic exclusion of some rows when a filter is used/triggered

[difar]

Hello everyone! I have a big trouble that seems fair away from resolution. 

I have a script that loads some information from a MSSQL via classic SQL load. The structure of the table is quite easy. ID, company_ID, customer_ID, article, QTY, price, discount and so far.

I need to categorize the companies behind the customers, so I do a load * inline.

load * inline [
business_type, company_ID
Italy, 4
World, 3
World, 2
World, 1
]

And this works like a charm. I can filter the "business_type" by creating an input object. But I need to exclude from any view/table/graph/pivot some customer_IDs when business_type is "Italy".

So, if I select "Italy" as business type the customers with ID 123, 456, 789 should be excluded from visualization, but when "World" is selected those IDs should be back as normal.

Any hint?

[vincent_ardiet_]

Ok, you are renaming fields in your query, so of course this could not work. Try like this:

MainTable:

LOAD
*,
autonumberhash128(COMPANY_ID, CLIENTE_ID) as COMPANY_CLIENTE_ID;
SQL SELECT dbo.BI_TEST_CUSTOMERS.POSTAL_CODE AS CAP, 

           dbo.BI_TEST_CUSTOMERS.ERP_ID AS CODICE_GESTIONALE, 

    dbo.BI_TEST_BODY.CUSTOMER_ID CLIENTE_ID, 
    dbo.BI_TEST_BATCH.COMPANY_ID, 
    dbo.BI_TEST_COMPANIES.BUSINESS_NAME AS DISTRIBUTORE, 
    dbo.BI_TEST_BATCH.START_DATE, 
    dbo.BI_TEST_BATCH.END_DATE, 
           dbo.BI_TEST_BODY.BATCH_ID, 
    dbo.BI_TEST_BODY.DOCUMENT_TYPE_ID, 
    dbo.BI_TEST_BODY.SALESAGENT_ID AGENTE_ID, 
    dbo.BI_TEST_BODY.HAS_DESTINATION, 
    dbo.BI_TEST_BODY.BLOCK, 
           dbo.BI_TEST_BODY.DOCUMENT_NUMBER AS DOCUMENTO_NUMERO, 
    FORMAT(dbo.BI_TEST_BODY.DOCUMENT_DATE, 'dd-MM-yyyy') AS DOCUMENTO_DATA, 
    dbo.BI_TEST_BODY.TOWN AS PROVINCIA, 
    dbo.BI_TEST_BODY.CODART, 
    CONCAT(TRIM(dbo.BI_TEST_BODY.CODART), ' - ', TRIM(dbo.BI_TEST_ARTICLES.DESCART)) AS ARTICOLO, 
    CONCAT(TRIM(dbo.BI_TEST_CUSTOMERS.TOWN), ' - ', 
    TRIM(dbo.BI_TEST_CUSTOMERS.POSTAL_CODE) , ' - ', 
    TRIM(dbo.BI_TEST_CUSTOMERS.CITY), ' - ', 
    TRIM(dbo.BI_TEST_CUSTOMERS.STREET)) AS INDIRIZZO_COMPLETO,
    CASE 
 WHEN DOCUMENT_TYPE_ID = 'F' THEN dbo.BI_TEST_BODY.QTY 
 WHEN DOCUMENT_TYPE_ID = 'N' THEN 0 -dbo.BI_TEST_BODY.QTY
 END QTY, 
 dbo.BI_TEST_BODY.PUBLIC_PRICE AS PREZZO_PUBBLICO, 
 dbo.BI_TEST_ARTICLES.DESCART AS ARTICOLO_DESCRIZIONE, 
 dbo.BI_TEST_ARTICLES.BRAND_NAME AS MARCA, 
 dbo.BI_TEST_BODY.DISCOUNT AS SCONTO, 
 CASE 
 WHEN DOCUMENT_TYPE_ID = 'F' THEN dbo.BI_TEST_BODY.NET 
 WHEN DOCUMENT_TYPE_ID = 'N' THEN 0 -dbo.BI_TEST_BODY.NET
 END PREZZO_NETTO,
 CASE 
 WHEN DOCUMENT_TYPE_ID = 'F' THEN dbo.BI_TEST_BODY.NETTOTAL 
 WHEN DOCUMENT_TYPE_ID = 'N' THEN 0 -dbo.BI_TEST_BODY.NETTOTAL
 END PREZZO_NETTO_TOTALE,
 YEAR(dbo.BI_TEST_BODY.DOCUMENT_DATE) AS ANNO, 
 MONTH(dbo.BI_TEST_BODY.DOCUMENT_DATE) AS MESE, 
 MONTH(dbo.BI_TEST_BODY.DOCUMENT_DATE) AS MESENUMERICO, 
 DAY(dbo.BI_TEST_BODY.DOCUMENT_DATE) AS GIORNO, 
 dbo.BI_TEST_CUSTOMERS.CUSTOMER_TYPE, 
 dbo.BI_TEST_CUSTOMERS.BUSINESS_NAME AS CLIENTE_RAGIONE_SOCIALE, 
 REPLACE(dbo.BI_TEST_CUSTOMERS.VAT,' ', '') AS 'PARTITA IVA',
 dbo.BI_TEST_AGENTS.name AGENTE
 FROM    dbo.BI_TEST_COMPANIES RIGHT OUTER JOIN
                dbo.BI_TEST_BATCH ON dbo.BI_TEST_COMPANIES.ID = dbo.BI_TEST_BATCH.COMPANY_ID LEFT OUTER JOIN
                dbo.BI_TEST_BODY LEFT OUTER JOIN
                dbo.BI_TEST_ARTICLES ON dbo.BI_TEST_BODY.CODART = dbo.BI_TEST_ARTICLES.CODART LEFT OUTER JOIN
                dbo.BI_TEST_CUSTOMERS ON dbo.BI_TEST_BODY.CUSTOMER_ID = dbo.BI_TEST_CUSTOMERS.ID ON dbo.BI_TEST_BATCH.ID = dbo.BI_TEST_BODY.BATCH_ID             
        LEFT OUTER JOIN
                dbo.BI_TEST_AGENTS ON dbo.BI_TEST_BODY.SALESAGENT_ID = dbo.BI_TEST_AGENTS.agent_ID AND 
                dbo.BI_TEST_BODY.COMPANY_ID = dbo.BI_TEST_AGENTS.company_ID 
        WHERE (dbo.BI_TEST_CUSTOMERS.ERP_ID <> 4746 AND dbo.BI_TEST_CUSTOMERS.ERP_ID <> 9662); 

tmpBusinessTypes:
load * inline [
 business_type, COMPANY_ID
 WORLD, 4
 WORLD, 3
 WORLD, 2
 WORLD, 1
 ITALIA, 4
];
Inner Join (tmpBusinessTypes)
Load Distinct COMPANY_ID, CLIENTE_ID, COMPANY_CLIENTE_ID Resident MainTable;

mapExceptions:
Mapping Load *, 1 as Exception inline [
CLIENTE_ID
1161
1880
1800 
];

BusinessTypes:
Load COMPANY_CLIENTE_ID, If(ApplyMap('mapExceptions',CLIENTE_ID,0),'ITALIA',business_type) as business_type Resident tmpBusinessTypes;