Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bvssudhakar
Contributor II

“Where” clause in Expressions

Hello Experts

Here I am having two doubts can you guys please help me from below mentioned doubts:

1.How to write the “where” clause in Expressions


For example:

I want count(candidateID) where placementdate is not null (can you guys elaborate this please)


2. How to change the names of values in our columns


For Example:

I have one column named as Status, In that Column values are 1,0 now i want to replace those values with open, close. i am not getting the idea how to change the values if it is excel we can modify but i am taking query from Sql Server Database.



Please Help out from this as soon as possible guys



Thank you in advance

1 Solution

Accepted Solutions

Re: “Where” clause in Expressions

Try like this

If(Status = 1, 'Open', 'Close) as Status


or


Pick(Match(Status, 1, 0), 'Open', 'Close') as Status


But you need to do this in the LOAD statement and not the SQL statement

16 Replies
bvssudhakar
Contributor II

Re: “Where” clause in Expressions

Re: “Where” clause in Expressions

1) You can use set analysis or if statement

Set Analysis

Count({<placementdate = {"*"}>}candidateID)

If statement

Count(If(Len(Trim(placementdate)) <> 0, candidateID)

or

Count(If(not IsNull(placementdate), candidateID)

Re: “Where” clause in Expressions

2) You can use if statement or Pick Match

If statement

If(Status = 1, 'Open', 'Close)

Pick Match

Pick(Match(Status, 1, 0), 'Open', 'Close')

Re: “Where” clause in Expressions

You can try this way?

For first one,

Count({<placementdate -= {"=Null()"}>} candidateID)

Or

Count({<placementdate -= {"-"}>} candidateID)

Or

Count({<placementdate -= {""}>} candidateID)

Or

Count({<placementdate -= {"=Len(placementdate)=0"}>} candidateID)

Or

Count(If(placementdate<>Null(), candidateID))

For second one,

Pick(Match(Status,1,0),'open','close')

Or

If(Status=1,'open', If(Status=0,'close')) as Status

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
bvssudhakar
Contributor II

Re: “Where” clause in Expressions

Hi Sunny,

I tried Set Analysis. It's Working.

Now I will implement it in my original data

Thank you for giving the answer

bvssudhakar
Contributor II

Re: “Where” clause in Expressions

Hi Sunny,

For this second one i want to write it in script page because i have loaded sql query in script in that i have this status column is there.

That is the major list box for me. In that list box values will be visible as open, close.


I tried these are in Script but it is showing error

Re: “Where” clause in Expressions

Sounds good

Re: “Where” clause in Expressions

Try like this

If(Status = 1, 'Open', 'Close) as Status


or


Pick(Match(Status, 1, 0), 'Open', 'Close') as Status


But you need to do this in the LOAD statement and not the SQL statement

bvssudhakar
Contributor II

Re: “Where” clause in Expressions

I tried  this Pick(Match(Status, 1, 0), 'Open', 'Close') in List box properties it's working but one thing it is showing as 1 open, 0 close.

I don't want to show 1,0, I want to show only open, close