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_ · Accepted solution]

Do you want to see Italian companies when you select business type "World" or just those 3 clients?
If not, something like this should work:

MainTable:
Load ID, company_ID, customer_ID, autonumberhash128(company_ID, customer_ID) as comp_cust_ID,  article, QTY, price, discount...

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

Inner Join (tmpBusinessTypes)
Load Distinct company_ID, customer_ID, comp_cust_ID Resident MainTable;

mapExceptions:
Mapping Load *, 1 as Exception inline [
customer_ID
123
456
789
];

BusinessTypes:
Load comp_cust_ID, If(ApplyMap('mapExceptions',customer_ID,0),'World',business_type) as business_type Resident tmpBusinessTypes;

Drop Table tmpBusinessTypes;

[difar]

Thanks @vincent_ardiet_ for your quick reply.

---

@difar wrote:

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?

Do you want to see Italian companies when you select business type "World" or just those 3 clients? [...]

---

When I select "World" i need to see all customers except three. When I switch back to "Italy" I need to see all those customers who fits into the:

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

[vincent_ardiet_]

Sorry not clear to me.

First you wrote: "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."

Then: "When I select "World" i need to see all customers except three. When I switch back to "Italy" I need to see all those customers who fits into..."

This is a bit confusing.

[difar]

Sorry, I wasn't clear. My fault. Your first response was clear and perfect and solved my problem! Thank you so much @vincent_ardiet_ 

[difar]

Sorry, my fault. I did many tests and all ends in a big mess.  I tried to adapt your code, but it seems that nothing happens. I wasn't clear at all, and I am trying to do my best now.

The script

This is the script. I am not loading the MainTable as follows but, instead, from a MSSQL server.

MainTable:
LOAD * INLINE [
ID, company_ID, customer_ID, article, QTY, price, discount, comp_cust_ID, business_name
1, 1, 123, 'ProductA', 10, 100, 0.1, 'FASV','UEGLERS JAMES'
2, 2, 456, 'ProductB', 15, 150, 0.2, 'FASC', 'KRETZ JAPOIVOC'
7, 3, 456, 'ProductX', 15, 150, 0.2, 'SESC', 'SET KELFH'
3, 4, 789, 'DELETEME', 20, 200, 0.15, 'AASV', 'INTERSO MICHELE -INTERNO-'
4, 4, 123, 'ProductD', 25, 250, 0.3, 'HESV', 'GIACOMI BINI'
5, 4, 456, 'ProductE', 30, 300, 0.25, 'FRKJ', 'LUANA CRESCENZO'
6, 4, 789, 'ProductF', 35, 350, 0.4, 'PVWER', 'FILIPPO FEDDINA'
];

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

Inner Join (tmpBusinessTypes)
Load Distinct company_ID, customer_ID, comp_cust_ID Resident MainTable;

mapExceptions:
Mapping Load *, 1 as Exception inline [
customer_ID
3
];


BusinessTypes:
Load comp_cust_ID, If(ApplyMap('mapExceptions',customer_ID,0),'World',business_type) as business_type Resident tmpBusinessTypes;

Drop Table tmpBusinessTypes;

Purpose of the script

The field business_type is created to categorize customers by their company_ID. So, I can easily create a pivot/table/map that geographically helps me to visualize sales. I can easily split sales by Italy and Word (including Italy).

But I need to exclude some customers when business_type is set, in any dashboard, as "World". The pseudologic is this "Ok, if we are seeing the world I want to see all customers except those with ID 3, 5, 6".

For testing purpose, in the script, I excluded only this one with ID = 3:

3, 4, 789, 'DELETEME', 20, 200, 0.15, 'AASV', 'INTERSO MICHELE -INTERNO-'

which belongs to Italy (via company_ID = 4) but it shound be excluded dinamically when business_type is set to "World".

Currently this script doesn't remove this customer...

Questions

- Is it possible to exclude, directly in the script, some IDs when a filter (business_type) changes its selected option?

- Since all the data are loaded via "SQL" load should I adapt the script?

[vincent_ardiet_]

There seems to still be some confusions in your description as your are using 3 as a customer_ID to be excluded but your customer_ID in the main table are 123, 456 and 789.

Whatever, if we supposed that mapExceptions contains a proper list of customer_ID, the last part should be then:
BusinessTypes:
Load comp_cust_ID, business_type
Resident tmpBusinessTypes
Where Not (business_type='World' and ApplyMap('mapExceptions',customer_ID,0));

And regarding the fact that you are loading from a DB, the first statement will be something like:
MainTable:
Load *, autonumberhash128(company_ID, customer_ID) as comp_cust_ID;
SQL Select ...

[difar]

Hello and thanks again, unfortunately this is not working by my side. Upgrading my script results in several errors (script error proposed me by msgbox). And the IDs are not filtered when I switch from Italy to World and viceversa.

- Field not found error.  Field 'company_ID' not found.
- Field not found error.  Field 'comp_cust_ID' not found

I see that you suggested me to insert a line between MainTable and the SQL Query as follow:

MainTable:
Load *, autonumberhash128(company_ID, customer_ID) as comp_cust_ID;
SQL SELECT [...],

But this raises a script error

Table not found error

Table 'MainTable' not found

Inner Join (tmpBusinessTypes)
Load Distinct company_ID, customer_ID, comp_cust_ID Resident MainTable

The Flow

1. OLEDB CONNECT TO [...] // The OLEDBD connection to MSSQL server
2. MainTable:
   SQL SELECT dbo.[...] // The query that gets data from server
3. tmpBusinessTypes:
   load * inline [ business_type, company_ID  ]
   Inner Join (tmpBusinessTypes)
   Load Distinct company_ID, customer_ID, comp_cust_ID Resident MainTable;
4. mapExceptions:
   Mapping Load *, 1 as Exception inline [
   customer_ID
   123,
   456,
   789
   ]; // The IDs I want to exclude 
5. BusinessTypes:
   Load comp_cust_ID, If(ApplyMap('mapExceptions',customer_ID,0),'ITALIA',business_type) as business_type Resident tmpBusinessTypes;

[vincent_ardiet_]

I don't see what could go wrong, just replace the INLINE part with you SQL part and this shouldn't generate an error. Else, put your code.

MainTable:
LOAD
*,
autonumberhash128(company_ID, customer_ID) as comp_cust_ID
INLINE [
ID, company_ID, customer_ID, article, QTY, price, discount, business_name
1, 1, 123, 'ProductA', 10, 100, 0.1, 'UEGLERS JAMES'
2, 2, 456, 'ProductB', 15, 150, 0.2, 'KRETZ JAPOIVOC'
7, 3, 456, 'ProductX', 15, 150, 0.2, 'SET KELFH'
3, 4, 789, 'DELETEME', 20, 200, 0.15, 'INTERSO MICHELE -INTERNO-'
4, 4, 123, 'ProductD', 25, 250, 0.3, 'GIACOMI BINI'
5, 4, 456, 'ProductE', 30, 300, 0.25, 'LUANA CRESCENZO'
6, 4, 789, 'ProductF', 35, 350, 0.4, 'FILIPPO FEDDINA'
];

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

Inner Join (tmpBusinessTypes)
Load Distinct company_ID, customer_ID, comp_cust_ID Resident MainTable;

mapExceptions:
Mapping Load *, 1 as Exception inline [
customer_ID
789
];

BusinessTypes:
Load comp_cust_ID, business_type
Resident tmpBusinessTypes
Where Not (business_type='World' and ApplyMap('mapExceptions',customer_ID,0));

Drop Table tmpBusinessTypes;

[difar]

Thanks, this is my full script:

MainTable:

LOAD
*,
autonumberhash128(company_ID, customer_ID) as comp_cust_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, customer_ID, comp_cust_ID Resident MainTable;

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

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