Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
bouderbc
New Contributor III

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
s_karpusenko
New Contributor

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
New Contributor III

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_dimperi
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'

s_karpusenko
New Contributor

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
New Contributor III

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

Community Browser