Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

bouderbc
Contributor

how to make a conditional filtering in the load script ?

i have many values to filter in the same column , what should i do to  filter these values in the load script ?

Case 1:

Example Column : Groupe_Name

Values to filter : KB , PILOTE, COMPUTER,pilote

From: ......XLS

And what is the best way to filter data in qlik view ?

Thanks a lot

Regards

Ali

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: how to make a conditional filtering in the load script ?

all great approaches. I'll just add another one into the mix, using Where Exists() or Where Not Exists() clauses.

First you would load up a table with one field, listing out which values you want to filter out, for example. This can be an inline load, a read from an Excel file or a DB, whatever works for you. And then you'd use this filter field in the Where clause of the actual data load. Like so:

FilterTabel:

Load * Inline [

     Groupe_Name

     KB

     PILOTE

     COMPUTER

     pilote

];

ActualData:

Load *

From [filename.xls]

Where Not Exists(Groupe_Name);

DROP Tabel FilterTable;

8 Replies
prat1507
Valued Contributor

Re: how to make a conditional filtering in the load script ?

Load * from

[.xls]

where Groupe_Name='KB' 

or Groupe_Name='PILOTE'

or Groupe_Name='COMPUTER'

or Groupe_Name='pilote'

bouderbc
Contributor

Re: how to make a conditional filtering in the load script ?

But i don't wanna show this values , it's the opposite , i want to import my data filtred without  KB , PILOTE, COMPUTER,pilote

Re: how to make a conditional filtering in the load script ?

This should worked for you


Load

*

From Source where


Match(Groupe_Name, 'KB' , 'PILOTE', 'COMPUTER','pilote');

vvira1316
Valued Contributor II

Re: how to make a conditional filtering in the load script ?

where Groupe_Name<>'KB' 

and Groupe_Name<>'PILOTE'

and Groupe_Name<>'COMPUTER'

and Groupe_Name<>'pilote'

eduardo_dimperio
Valued Contributor II

Re: how to make a conditional filtering in the load script ?

Adapting prat1507 answer to not show:

Load * from

[.xls]

where

Groupe_Name<>'KB'

or Groupe_Name<>'PILOTE'

or Groupe_Name<>COMPUTER'

or Groupe_Name<>'pilote'

Partner
Partner

Re: how to make a conditional filtering in the load script ?

all great approaches. I'll just add another one into the mix, using Where Exists() or Where Not Exists() clauses.

First you would load up a table with one field, listing out which values you want to filter out, for example. This can be an inline load, a read from an Excel file or a DB, whatever works for you. And then you'd use this filter field in the Where clause of the actual data load. Like so:

FilterTabel:

Load * Inline [

     Groupe_Name

     KB

     PILOTE

     COMPUTER

     pilote

];

ActualData:

Load *

From [filename.xls]

Where Not Exists(Groupe_Name);

DROP Tabel FilterTable;

vvira1316
Valued Contributor II

Re: how to make a conditional filtering in the load script ?

where not Match(Groupe_Name, 'KB' , 'PILOTE', 'COMPUTER','pilote');

bouderbc
Contributor

Re: how to make a conditional filtering in the load script ?

Thank you Vijay,

i also find a new method to make a conoditional removing of data , using Qlik view wizard

Directory;

LOAD @1,

     @2,

     @3,

     @4,

     @5,

     @6,

     @7,

     @8,

     @9,

     @10,

     @11

FROM

[..\QVD\Toto.QVD]

(qvd, filters(

Remove(Row, RowCnd(CellValue, 3, StrCnd(contain, 'Apples'))),

Remove(Row, RowCnd(CellValue, 3, StrCnd(contain, 'Fruits'))),

Remove(Row, RowCnd(CellValue, 3, StrCnd(contain, 'Car'))),

Remove(Row, RowCnd(CellValue, 3, StrCnd(contain, 'computer')))));

Regards

Ali