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: 
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
4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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
sunny_talwar

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III

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