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?
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;