Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Problem with where clause

Hi All,

I am stuck at some point in development process and would need you help and suggestions.

My excel file contains data in the format as specified below.   

NameProd/Non ProdActivity
AmitProdCall
AmitProdMeeting
AmitProdTest
AmitNon ProdBreak
MonikaProdCall
MonikaNon ProdBreak
MonikaNon ProdGames
MonikaNon Prodcheck mail
PriyankaProdQA
PriyankaProdCall
PriyankaNon ProdBreak
PriyankaNon Prodcheck mail
SarveshProdTest
SarveshProdMeeting
SarveshProdQA
SarveshNon ProdBreak
SumitProdCall
SumitProdTest
SumitProdMeeting
SumitNon ProdBreak
VikasNon ProdBreak
VikasProdQA
VikasNon Prodcheck mail
VikasNon ProdGames

   

I need to load data on following conditions only

  1. Remove break from Activity column for all Names.
  2. For Name = 'Priyanka' and 'Vikas' remove all 'Non Prod' Values
  3. For Name = 'Priyanka' and 'Vikas' remove all QA columns
  4. Except above conditions all rows must be loaded
Tags (1)
4 Replies
MVP
MVP

Re: Problem with where clause

Where

  Activity <> 'Break'

  And (Match(Name, 'Priyanka', 'Vikas') = 0 Or ([Prod/Non Prod] <> 'Non Prod' And Activity <> 'QA'))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Problem with where clause

Try this:

Table:

LOAD Name,

     [Prod/Non Prod],

     Activity

FROM

[https://community.qlik.com/thread/204640]

(html, codepage is 1252, embedded labels, table is @1)

Where Activity <> 'Break' and

   not(Match(Name, 'Priyanka', 'Vikas') and ([Prod/Non Prod] = 'Non Prod' or Activity = 'QA'));


Capture.PNG

MVP
MVP

Re: Problem with where clause

Or, if its slightly clearer, the functionally equivalent:

Where

  Activity <> 'Break'

  Not (Match(Name, 'Priyanka', 'Vikas') And ([Prod/Non Prod] = 'Non Prod' Or Activity = 'QA'))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
alex_millan
Contributor III

Re: Problem with where clause

Hi Priyanka,

Where

     Activity <> 'Break' and (

     Not(Match(Name, 'Priyanka', 'Vikas')

     Or

     (Match(Name, 'Priyanka','Vikas') And [Prod/Non Prod] <> 'Non Prod' And Activity <> 'QA')

     );

Regards

Community Browser