Forum Discussion
Helper IIINOOB Q! How to transform data across relationships
Helper IIIThanks, amitchandak! I guess the problem is that while I know how to transform data across a single table, I'm not connecting the dots on how to do it when the data I want to use to restrict during the import is spread across tables. Everything I've tried has only restricted the flow of data on the one particular table but had no impact on the records coming in on the Company table...
I can't use Company_Type_RecID to directly limit the records impoted from the [Company] table. This might be so basic that I'm over complicating it!
Any other suggestions?
Thanks again
This is the first step you take in BI, it's called ETL. We have to filter the trash
In your case it is done with nested queries in SQL. Imagine that I have a catalog of clients, historic, but prepare a study on the last year my main clients. The main customers based on what? If your answer is billing, then you should filter my customer catalog, based on multi-year invoices.
This query will give a single-column subtable with all customers (only their id) to the one that was billed last year and will be the filter for the Customers table
(SELECT DISTINCT IdCliente FROM TablaFactura WHERE (FechaFactura >= 01/01/2019 AND FechaFactura <= 31/12/2019))
Query to load customers.
SELECT (the fields you're interested in in the comma-separated customers table)
FROM TablaClientes
WHERE (SELECT DISTINCT IdCliente FROM TablaFactura WHERE (FechaFactura >= 01/01/2019 AND FechaFactura <= 31/12/2019))
I hope it helps.
Sorry there's an error in the query
SELECT (the fields you're interested in in the comma-separated customers table)
FROM TablaClientes
WHERE IdCliente IN (SELECT DISTINCT IdCliente FROM TablaFactura WHERE (FechaFactura >= 01/01/2019 AND FechaFactura <= 31/12/2019))