Optimized Load

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

Not sure where your Customers table comes from DB, Excel....?

Customers:

LOAD Customer_ID,

     Customer_Name

SQL SELECT *

FROM Customers

WHERE Customer_ActiveFlag='Y';

[Anonymous]

Thanks Bill - this worked well.  I appreciate your insight.

Kathleen

[Anonymous]

The Customer data is in a qvd which is generated from our TMS system.

[johnw]

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

[Anonymous]

Thanks John - this works too. 

Kathleen