Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm loading a customer table and some customers are active and others are not. There is an active customer field that is flagged with a Y for active and N for nonactive. How can I load the table, filter to only active customer and have an optimized load?
the table has the following fields:
Customer_ID
Customer_Name
Customer_ActiveFlag
Kathleen
Do an Inline Load to a temporary table with just one field [Customer_ActiveFlag] and just one row of value 'Yes'
Then do your load from qvd with a Where Exists clause on your [Customer_ActiveFlag] - this will be optimized.
Finally drop your temporary table
Do an Inline Load to a temporary table with just one field [Customer_ActiveFlag] and just one row of value 'Yes'
Then do your load from qvd with a Where Exists clause on your [Customer_ActiveFlag] - this will be optimized.
Finally drop your temporary table
Not sure where your Customers table comes from DB, Excel....?
Customers:
LOAD Customer_ID,
Customer_Name
SQL SELECT *
FROM Customers
WHERE Customer_ActiveFlag='Y';
Thanks Bill - this worked well. I appreciate your insight.
Kathleen
The Customer data is in a qvd which is generated from our TMS system.
I tend to use autogenerate (often with subfield to easily generate all combinations of multiple values in multiple fields, not relevant here), then inner join. Pretty much functionally identical. Just a slightly different way of doing the same thing.
[Customer]:
LOAD 'Y' as [Customer_ActiveFlag]
AUTOGENERATE 1
;
INNER JOIN ([Customer])
LOAD
[Customer_ID]
,[Customer_Name]
,[Customer_ActiveFlag]
FROM Customer.qvd (QVD)
WHERE exists([Customer_ActiveFlag])
;
Vs.
[Temp]:
LOAD * INLINE [
Customer_ActiveFlag
Y
];
[Customer]:
LOAD
[Customer_ID]
,[Customer_Name]
,[Customer_ActiveFlag]
FROM Customer.qvd (QVD)
WHERE exists([Customer_ActiveFlag])
;
DROP TABLE [Temp];
Thanks John - this works too.
Kathleen