Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jim_chan
Specialist
Specialist

How to convert this SQL to qlikview expression

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Hi Jim,

Try,

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

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

      )

Change the field names accordingly.

View solution in original post

8 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
tamilarasu
Champion
Champion

Hi Jim,

Try,

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

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

      )

Change the field names accordingly.

Anonymous
Not applicable

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

jim_chan
Specialist
Specialist
Author

thank you teacher! very well said!

Kushal_Chawda

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

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

jim_chan
Specialist
Specialist
Author

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

tamilarasu
Champion
Champion

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

jim_chan
Specialist
Specialist
Author

thanks my friend!