topic Re: “Where” clause in Expressions in QlikView

“Where” clause in Expressions

bvssudhakar — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: “Where” clause in Expressions

bvssudhakar — Mon, 06 Aug 2018 13:46:20 GMT

stalwar1‌

youssefbelloum‌

sifatnabil‌mto‌

Re: “Where” clause in Expressions

sunny_talwar — Mon, 06 Aug 2018 13:46:32 GMT

1) You can use set analysis or if statement

Set Analysis

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

If statement

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

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

Re: “Where” clause in Expressions

sunny_talwar — Mon, 06 Aug 2018 13:47:44 GMT

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

Anil_Babu_Samineni — Mon, 06 Aug 2018 13:52:58 GMT

You can try this way?

For first one,

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

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

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

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

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

For second one,

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

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

Re: “Where” clause in Expressions

bvssudhakar — Mon, 06 Aug 2018 14:09:00 GMT

Hi Sunny,

I tried Set Analysis. It's Working.

Now I will implement it in my original data

Thank you for giving the answer

Re: “Where” clause in Expressions

bvssudhakar — Mon, 06 Aug 2018 14:12:23 GMT

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

sunny_talwar — Mon, 06 Aug 2018 14:12:40 GMT

Sounds good

Re: “Where” clause in Expressions

sunny_talwar — Mon, 06 Aug 2018 14:13:26 GMT

Try like this

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

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

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

Re: “Where” clause in Expressions

bvssudhakar — Mon, 06 Aug 2018 14:19:26 GMT

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

Re: “Where” clause in Expressions

sunny_talwar — Mon, 06 Aug 2018 14:27:40 GMT

I hope you are doing this on the general tab and not the expressions tab?

Re: “Where” clause in Expressions

bvssudhakar — Mon, 06 Aug 2018 14:35:02 GMT

I tried in load script also but it's not showing in fields pane, can you check the attached images it is correct or wrong

Re: “Where” clause in Expressions

sunny_talwar — Mon, 06 Aug 2018 14:37:34 GMT

So, you need to use Preceding Load here

RMS:

LOAD *,

Pick(Match(ishotjob, -1, 0), 'Open', 'Close') as JobStatus;

SELECT ....

Re: “Where” clause in Expressions

bvssudhakar — Mon, 06 Aug 2018 14:42:00 GMT

Hi Anil,

I tried this formula Count({<placementdate -= {"=Null()"}>} candidateID)

This is also working

Thank you for your help also

Re: “Where” clause in Expressions

bvssudhakar — Mon, 06 Aug 2018 14:47:35 GMT

Hi Sunny,

Thank you, I forgot the pattern thank you for the help now it is working.

Now i have two more questions i will create separate discussion for them

And For which answer i want give the correct answer,... for this one or previous 1st question

Re: “Where” clause in Expressions

sunny_talwar — Mon, 06 Aug 2018 14:52:58 GMT

That is for you to decide... but you can always mark helpful responses for any/all answers which you thought were helpful.

Best,

Sunny

Re: “Where” clause in Expressions

debabrata_sahoo — Wed, 26 Jun 2019 05:38:55 GMT

In pick match case how can we use ELSE statement.