topic Re: How to convert this SQL to qlikview expression in QlikView

How to convert this SQL to qlikview expression

jim_chan — Tue, 29 Mar 2016 05:41:26 GMT

Hi guys,

I am finding it hard time to understand this SQL server report builder expression and convert it to qlikview expression.

=IIF(Fields!System1FinishDate.Value > Now Or Fields!System0FinishDate.Value > Now, 3,

IIF(isNothing(Fields!Software_FinalDate.Value),2,1))

Anyone here understand this? or any site that can help me convert this ?

Rgds,

Jim

Re: How to convert this SQL to qlikview expression

Gysbert_Wassenaar — Tue, 29 Mar 2016 05:57:29 GMT

If the value of Fields!System1FinishDate.Value is larger that the current time or the value of Fields!System0FinishDate.Value is larger than the current time the result is 3, but otherwise if Fields!Software_FinalDate.Value has no value then the result is 2. In all other cases the result is 1.

The Qlik equivalent is:

If( Fields!System1FinishDate.Value > Now() or, Fields!System0FinishDate.Value > Now(), 3,if( IsNull( Fields!System0FinishDate.Value),2,1 ) )

Re: How to convert this SQL to qlikview expression

tamilarasu — Tue, 29 Mar 2016 06:01:47 GMT

Hi Jim,

Try,

=IF(FieldName1 > Now() Or FieldName2 > Now () , 3,

IF(Len(Trim(FieldName3 ))=0 , 2,1 )

)

Change the field names accordingly.

Re: How to convert this SQL to qlikview expression

Anonymous — Tue, 29 Mar 2016 06:02:21 GMT

Hi Jim,

I am not 100% sure but I think something like this:

if(System1FinishDate > now() OR System0FinishDate > now(), 3,

if(IsNull(Software_FinalDate),2,1)

) as Expression

Re: How to convert this SQL to qlikview expression

jim_chan — Tue, 29 Mar 2016 06:08:09 GMT

thank you teacher! very well said!

Re: How to convert this SQL to qlikview expression

Kushal_Chawda — Tue, 29 Mar 2016 06:09:19 GMT

if(System1FinishDate > now() OR System0FinishDate > now(), 3,

if(len(trim(Software_FinalDate))=0,2,1)) as NewField

Re: How to convert this SQL to qlikview expression

jim_chan — Tue, 29 Mar 2016 06:09:50 GMT

Hello Tamil!

is you again bro! well yeah, i tried your advise as well. worked well so far!

can you let me know.. why for the last field3 - have to use if(len(trim) ?

Rgds,

Jim

Re: How to convert this SQL to qlikview expression

tamilarasu — Tue, 29 Mar 2016 06:29:46 GMT

Hi Jim,

I have used Len(Trim()) to capture empty spaces as well. Isnull() function returns 0 (False) for blank spaces but Len(Trim(FieldName)) will return 0. Below link might be helpful.

IsNull(MyField) vs. Len(MyField) = 0

Re: How to convert this SQL to qlikview expression

jim_chan — Tue, 29 Mar 2016 06:34:27 GMT

thanks my friend!