Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

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

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

View solution in original post

16 Replies
Highlighted
Creator III
Creator III

Highlighted

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)

Highlighted

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

Highlighted

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

Hi Sunny,

I tried Set Analysis. It's Working.

Now I will implement it in my original data

Thank you for giving the answer

Highlighted
Creator III
Creator III

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

Highlighted

Sounds good

Highlighted

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

View solution in original post

Highlighted
Creator III
Creator III

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