Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bouderbc
Creator
Creator

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

1 Solution

Accepted Solutions
s_karpusenko
Partner - Contributor
Partner - Contributor

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;

View solution in original post

8 Replies
prat1507
Specialist
Specialist

Load * from

[.xls]

where Groupe_Name='KB' 

or Groupe_Name='PILOTE'

or Groupe_Name='COMPUTER'

or Groupe_Name='pilote'

bouderbc
Creator
Creator
Author

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

its_anandrjs

This should worked for you


Load

*

From Source where


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

vvira1316
Specialist II
Specialist II

where Groupe_Name<>'KB' 

and Groupe_Name<>'PILOTE'

and Groupe_Name<>'COMPUTER'

and Groupe_Name<>'pilote'

eduardo_dimperio
Specialist II
Specialist II

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
Partner - Contributor
Partner - Contributor

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
Specialist II
Specialist II

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

bouderbc
Creator
Creator
Author

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