“Where” clause in Expressions

[bvssudhakar]

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

[bvssudhakar]

stalwar1‌

youssefbelloum‌

sifatnabil‌mto‌

[sunny_talwar]

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)

[sunny_talwar]

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

[Anil_Babu_Samineni]

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

[bvssudhakar]

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]

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

[sunny_talwar]

Sounds good

[sunny_talwar · Accepted solution]

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]

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