Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
Partner

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
Partner

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

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