topic Re: Filter in load script in QlikView

Filter in load script

igalbald — Fri, 06 Nov 2020 13:29:38 GMT

Hello,

I have two sources of data, one loading millions of lines and another one with only the line I am looking for.

Both have a filed of part number which I am using to associate the two tables.

To make sure I see only the part numbers relevant to me, I created another column in my excel table with all cells containing the same value.

In analysts, I created a filter on this filed. When I select the only value it contains, the view goes from million part numbers to the list I required. The challenge is that the filter will reset every time I open the app.

How can I make this filter permanent or include it in the script?

Thank you

Re: Filter in load script

dplr-rn — Fri, 06 Nov 2020 14:13:37 GMT

use exists function.

step 1 load your inclusion id

step 2 load main table with exists function.

step 3 drop table from step1

e.g.

ValidIDs: LOAD ValidIDs FROM [lib://Downloads/Custom.xlsx] (ooxml, embedded labels, table is MyValid); MainData: LOAD ID, Column1, Column2 FROM [lib://Downloads/Custom.xlsx] (ooxml, embedded labels, table is Main) where exists(ValidIDs,ID); drop table ValidIDs;

Re: Filter in load script

bhavtan — Fri, 06 Nov 2020 14:23:00 GMT

example from help site

site

LOAD * inline

[ Employee|ID|Salary

Bill|001|20000

John|002|30000

Steve|003|35000 ] (delimiter is '|');

Citizens:

Load * inline

[

Employee|Address

Bill|New York Mary|London

Steve|Chicago Lucy|Madrid

Lucy|Paris John|Miami ]

(delimiter is '|') where Exists (Employee);

Drop Tables Employees;

Re: Filter in load script

igalbald — Fri, 06 Nov 2020 15:01:35 GMT

The two tables I have include different fields that I need to keep, so I cannot drop the table.

The excel table I created includes a filed that includes a single value that I use to filer in the analysis tab.

I am looking to do this filter in the load script section.

Is it possible?

Thank you

Re: Filter in load script

dplr-rn — Fri, 06 Nov 2020 15:02:51 GMT

not 100% clear what you mean. but if you want to retain the table just dont drop the table.

excludes still works. (even in the sample script shared above.)

Re: Filter in load script

igalbald — Mon, 16 Nov 2020 10:12:08 GMT

I found a way to perform a permanent filter in the GUI.
Thank you