topic Re: Optimized Load in QlikView

Optimized Load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Optimized Load

Anonymous — Fri, 01 Apr 2016 18:39:07 GMT

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

Re: Optimized Load

Anonymous — Fri, 01 Apr 2016 18:39:29 GMT

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

Customers:

LOAD Customer_ID,

Customer_Name

SQL SELECT *

FROM Customers

WHERE Customer_ActiveFlag='Y';

Re: Optimized Load

Anonymous — Fri, 01 Apr 2016 19:28:11 GMT

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

Kathleen

Re: Optimized Load

Anonymous — Fri, 01 Apr 2016 19:29:01 GMT

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

Re: Optimized Load

johnw — Fri, 01 Apr 2016 20:16:07 GMT

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

Re: Optimized Load

Anonymous — Fri, 01 Apr 2016 20:44:15 GMT

Thanks John - this works too.

Kathleen