topic Re: "Else" part not working in QlikView

"Else" part not working

Thu, 16 Jan 2014 11:21:45 GMT

Hi All,

This might be something very simple, but can't get it to work, don't know why.

I have if-else calculation and many more like the same in my script, when I am pulling the columns in a straight table (alongwith few other columns), what I observed is, qlikview won't show me the results from else part even though the condition is fulfilled for it.

For Ex:

if (isnull([Phase 2 ST_DT]), ((max([Phase 2 E_DT]) - [Actual C_DT])/30), ((max([Phase 2 E_DT]) - max([Phase 2 ST_DT]))/30)) as [2_Dur_shyamal]

It will show the value [2_Dur_shyamal] only when [Phase 2 ST_DT] is null, otherwise it will be blank.

On the other hand, if I am using these calculations individually as expressions, it works fine.

But I would like to have all these calculations done in script rather in expressions

Any ideas on this?

Re: "Else" part not working

salto — Thu, 16 Jan 2014 11:26:23 GMT

Hi,

maybe the problem relies in the IsNull(field) function. I suggest you to use len(trim(field))=0 instead of IsNul:

if (len(trim([Phase 2 ST_DT]))=0, ((max([Phase 2 E_DT]) - [Actual C_DT])/30), ((max([Phase 2 E_DT]) - max([Phase 2 ST_DT]))/30)) as[2_Dur_shyamal]

Hope this helps!

Re: "Else" part not working

Thu, 16 Jan 2014 11:33:37 GMT

Hi,

Thanks for the response.

I tried the same, but doesn't work.

Re: "Else" part not working

salto — Thu, 16 Jan 2014 11:36:52 GMT

Hi,

what if we change the conditions order:

if (len(trim([Phase 2 ST_DT]))>0, ((max([Phase 2 E_DT]) - max([Phase 2 ST_DT]))/30 , ((max([Phase 2 E_DT]) - [Actual C_DT])/30))) as [2_Dur_shyamal]

Re: "Else" part not working

Thu, 16 Jan 2014 11:44:19 GMT

Hi,

In that case it calculates the else part, so no matter what order I keep, it always seems to display the expression for the condition :

[Phase 2 ST_DT] = 0

Re: "Else" part not working

salto — Thu, 16 Jan 2014 11:46:15 GMT

Which values have you got in that field [Phase 2 ST_DT] ?

Re: "Else" part not working

sujeetsingh — Thu, 16 Jan 2014 11:48:18 GMT

Syamal actually while there is dimension in the chart or graph you are using hense use this condition as

if(sum([2ST_DT])<>0,(max([2E_DT])-max([2ST_DT]))

,max([2E_DT])-max(C_DT))

See the sample i have pasted.

Re: "Else" part not working

vikasmahajan — Thu, 16 Jan 2014 11:48:31 GMT

check for synax as per following expr

fabs(SUM({<FinancialYear=,Quarter=,MonthName=>} AGGR(if((Date(vDateForAgeing)-if(isnull(Date([DUE_DATE])),1,Date([DUE_DATE])) >=181),SUM({$<FinancialYear=,Quarter=,MonthName=,[Posting Date] ={"<=$(=MonthEnd(Max([Posting Date])))"}>}[Net Outstanding])),DOC_NO,Customer,Location))

/100000)

HTW

Vikas

Re: "Else" part not working

Thu, 16 Jan 2014 11:52:05 GMT

There are numeric values.

Re: "Else" part not working

Thu, 16 Jan 2014 11:53:22 GMT

Hi,

Thanks for your response.

But it's working fine when I am using them in expressions.

My problem is, I want to do all these calculations in script where it is causing issue.

Re: "Else" part not working

sudeepkm — Thu, 16 Jan 2014 11:59:23 GMT

Hi,

SALTO's provided solution is the best practice to handle null. Have you tried "not isNull()", instead of isNull.

It is just the other way. If it is not null what to do else what to do, you may also check the blanks.

like [Phase 2 ST_DT] = ''

I do not have any specific reasons to advice using not isnull() but that is still an option.

Re: "Else" part not working

sujeetsingh — Thu, 16 Jan 2014 12:13:55 GMT

See this sample

Re: "Else" part not working

miikkaqlick — Thu, 16 Jan 2014 12:18:02 GMT

I think that reason is in your datamodel.

First try this:

If( IsNull( [Phase 2 ST_DT] ) ,1,0) as [2 Dur...]

This way you can verify that Isnull and If-functions are working.

Next step is to use verify calculations in if-statement.

Hopefully this will help.

Br,

Miikka

Re: "Else" part not working

kiranmanoharrode — Thu, 16 Jan 2014 12:19:07 GMT

Try this will work,

IsNull([Phase 2 ST_DT]), ((max([Phase 2 E_DT]) - [Actual C_DT])/30), ((max([Phase 2 E_DT]) - max([Phase 2 ST_DT]))/30) as [2_Dur_shyamal]

Re: "Else" part not working

Anonymous — Thu, 16 Jan 2014 12:25:27 GMT

Hi Shyamal,

here my suggestion like first check what ever the null your writing.

Like =if(isnull([Phase 2 ST_DT]) or len([Phase 2 ST_DT])=0,1,0) AS Test Flag

suppose above one writing 1 and 0 means you have null value,

after do one thing take table box add what ever the else fields are using and then select a Test Flag=1 then check your fields values are getting side by side or differently.

according to my understand like both are not getting in side by side values.

like.

X Field Name	Y field Name
100	-
200	-
-	0.25
-	.36

thanks

shaik

Re: "Else" part not working

Fri, 17 Jan 2014 07:59:57 GMT

Hi,

Yes tried, still it somehow always goes to the part where [Phase 2 ST_DT] = 0 and displays that value only.

Re: "Else" part not working

Fri, 17 Jan 2014 08:03:00 GMT

Still the same result

Re: "Else" part not working

Thu, 23 Jan 2014 07:50:34 GMT

Hi All,

I have tried all the possible things (Atleast I think so!!!) but still can't get this thing working.

Now, i have replaced "IsNull" with "Len(Trim())" as suggested by SALTO but with the same result.

Any help would be greatly appreciated!!

Re: "Else" part not working

salto — Thu, 23 Jan 2014 07:54:43 GMT

Hi Shyamal,

can you please provide a sample file with the ([Phase 2 ST_DT]) fields' real values?

Thanks!

Re: "Else" part not working

Colin-Albert — Thu, 23 Jan 2014 08:15:35 GMT

Do you have a Group By clause in the load script?

If you are using an aggregation function in the load script, you will need to group the data for that load section.