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

how to make a conditional filtering in the load script ?

bouderbc — Thu, 28 Sep 2017 13:54:58 GMT

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

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

prat1507 — Thu, 28 Sep 2017 13:59:14 GMT

Load * from

[.xls]

where Groupe_Name='KB'

or Groupe_Name='PILOTE'

or Groupe_Name='COMPUTER'

or Groupe_Name='pilote'

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

bouderbc — Thu, 28 Sep 2017 14:02:21 GMT

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 ?

its_anandrjs — Thu, 28 Sep 2017 14:07:44 GMT

This should worked for you

Load

From Source where

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

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

vvira1316 — Thu, 28 Sep 2017 14:26:45 GMT

where Groupe_Name<>'KB'

and Groupe_Name<>'PILOTE'

and Groupe_Name<>'COMPUTER'

and Groupe_Name<>'pilote'

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

eduardo_dimperio — Thu, 28 Sep 2017 14:35:38 GMT

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'

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

s_karpusenko — Thu, 28 Sep 2017 14:48:40 GMT

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;

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

vvira1316 — Thu, 28 Sep 2017 15:12:48 GMT

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

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

bouderbc — Fri, 29 Sep 2017 11:47:12 GMT

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