How can I use an existing field to filter a SQL query in QlikView?

[mlarruda]

Hi, I have a huge table with millions of rows/registers and dozens of columns/fields, one of them being NAME. Due to the size of this table, I want to load only the rows/registers corresponding to a given set of names, I managed to create a table TabNames, with a column/field also named NAME, containing the desired names to be loaded, and wanted to do someting like this:

FullTab:
LOAD (...)
SELECT *
FROM MY TABLE
WHERE NAME in TabNames;

or

FullTab:
LOAD (...)
SELECT *
FROM MY TABLE
WHERE EXISTS (NAME);

or even

FullTab:
LOAD (...)
SELECT *
FROM MY TABLE
WHERE NAME IN (
               LOAD DISTINCT NME_ADVPARTEATIVA
               FROM TabNames);

But nothing worked. How can I do this load?

[MatheusC]

Hi,

And if you do it in the Load statement?

MY TABLE:

LOAD
*

Where Exists(NAME);

SELECT
*

FROM "MY TABLE";

- Regards, Matheus

[marcus_sommer]

The simplest approach would be like hinted to apply a where exists() within the preceding-load. But this would mean that at first all records are fetched from the data-base and then filtered on the Qlik-side.

Better would be to restrict the data-set already within the SQL and your first approach is already quite near to the most common way to solve such things but it needs a list of values, like:

FullTab:
LOAD (...)
SELECT *
FROM MY TABLE
WHERE NAME in('Name1', 'Name2', ...);

Such a list could be created in the script with something like:

t: load concat(Name, chr(39) & ',' & chr(39)) as NameList resident X;

let v = peek('NameList', 0, 't');

and then using the variable in the where-clause like:

... WHERE NAME in('$(v)');

Often is in() not the most performant function and it may also be restricted to the number of possible parameters. The only alternatives to it would be to transfer the filter-table directly into the data-base to apply them there, for example as an inner-join filter. A technically very simple way would be to store the filter-table as csv and then loading this csv into the data-base but administratively it could be quite difficult ...

[mlarruda]

Thanks for both. The suggestion worked but not as I wanted. My real data structure is more complicated than in the example, so I don't know what to do.

[marcus_sommer]

I would test it in several small steps. At first only applying manually a single field-value, then two values, then assigning them to a variable and using the variable - maybe in() isn't supported or the parameters need no/other quotes/delimiter.

Further such testing mustn't be done within the origin application else a small extra dummy application which only queried the single field with extra First/Top/Limit statements and you could very fast playing with various versions.

[mlarruda]

Can I show my full structure so you can help me to fit all requirements of my work?

[marcus_sommer]

No, the aim of the community is to provide help to self-help but not doing the work for others.

This task may seem currently quite urgent but be sure it's not important. Essential is to do the things yourself and to comprehend the approaches, to learn for next times ...

[mlarruda]

I understand your answer, but I have a complex data structure, many results to obtain, many tables to link, many data treatments to manage and I've been trying and testing (by myself) scripts for weeks. It's not an easy job and it's not like I want someone else to do my “homework”.

But that's okay, it seems the only thing I can do is keep trying and testing and hope that one day I can finish my work or find someone who can help me.

[marcus_sommer]

The most data-structures aren't really complex - at least not from the point of view of a BI tool. Because not normalized sql-structures are suitable else quite the opposite a largely de-normalized data-model is wanted (otherwise BI tools wouldn't exists and everything would be done within the data-bases itself).

Ideally the Qlik data-model is created as a star-scheme which means there is a single fact-table and n surrounding dimension-tables. It's a very simple approach. The facts are mostly concatenated to each other by harmonizing the field-names and data-structures as much as possible and missing/wrong data are added with join/mapping-approaches. It does not mean that it wouldn't be work but it are mostly simple transformations which could be added step by step - and the progress and it's working could be easily tracked - just adding one or two pivot/table-boxes and a few list-boxes in the UI. And a reduced data-set - records as well as columns - will speed up the response-times and increasing the overview in the beginning.

Even if in the end more sophisticated approaches are necessary each project should start in this way - and it's quite likely that it could be extended to more complexity. Starting with multiple fact-tables and/or any link-table approaches won't save any efforts else making things usually unnecessary complex - unless you are quite experienced and knows exactly what your are doing.