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
Highlighted
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)
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