Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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
];
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.
Sorry, I wasn't clear. My fault. Your first response was clear and perfect and solved my problem! Thank you so much @vincent_ardiet_
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.
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;
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...
- 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?
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 ...
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
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;
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;