Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ahmonah2017
Contributor III
Contributor III

Resolving syntax in script for '=, <,>,-='

Hello QV team,

Still a beginner and loving the learning process!

Your help would be awesome with this minor issue:

In the script, I am attempting to determine output in a new field by stating the following:

If Date 1 = Date 2; 'On Time'

If Date 1 > Date 2; 'Early'

If Date 1 < Date 2; 'Late'

If Date 1 <> Date 2; '  ' (keep blank).


Formula shown below:

IF([Renewal Quarter 3]=[Renewed On 3],'On Time',IF([Renewal Quarter 3]<[Renewed On 3],'Late',IF([Renewal Quarter 3]>[Renewed On 3],'Early',IF([Renewal Quarter 3]<>[Renewed On 3],'','')))) as [Renewal Status]


For the fourth condition, I've also attempted the following:

If Date 1 ='Not Renewed', '  ' (keep blank).


Formula shown below:

IF([Renewal Quarter 3]=[Renewed On 3],'On Time',IF([Renewal Quarter 3]<[Renewed On 3],'Late',IF([Renewal Quarter 3]>[Renewed On 3],'Early',IF([Renewal Quarter 3]='Not Renewed','','')))) as [Renewal Status]

The result for the last condition is shown below and is incorrect.

QV Community- error1.JPG

Any help would be appreciated!

Thanks!

1 Solution

Accepted Solutions
t_chetirbok
Specialist
Specialist

Hi Mona,

Quick question for you)

can 'Not Renewed' value be only in Renewed On 3 or in both fields?

as I understand from your formulas for creating this fields this value can be only in Renewed On 3.

if yes, than in your for formula you just need to change the name

If([Renewed On 3] = 'Not Renewed', '',

If([Renewal Quarter 3] = [Renewed On 3], 'On Time',

If([Renewal Quarter 3] < [Renewed On 3], 'Late',

If([Renewal Quarter 3] > [Renewed On 3], 'Early')))) as [Renewal Status]

an example

/////////////////////////////////////////////

load

    [Renewal Quarter 3]

    , [Renewed On 3]

    , If([Renewed On 3] = 'Not Renewed', '  ',   

    If([Renewal Quarter 3] = [Renewed On 3], 'On Time',

    If([Renewal Quarter 3] < [Renewed On 3], 'Late',

    If([Renewal Quarter 3] > [Renewed On 3], 'Early')))) as [Renewal Status]

;

load * Inline

[

Renewal Quarter 3,    Renewed On 3

2014-Q3,    Not Renewed   

2015-Q1,    Not Renewed   

2015-Q2,    Not Renewed   

2014-Q1,    Not Renewed

2017-Q2,    2016-Q3   

2016-Q1,    2016-Q3   

2016-Q1,    2016-Q3   

2016-Q1,    2016-Q3   

2016-Q3,    2016-Q3

2016-Q4,    2016-Q3

]

;

View solution in original post

17 Replies
sunny_talwar

Try this

If([Renewal Quarter 3] = 'Not Renewed', '  ',

If([Renewal Quarter 3] = [Renewed On 3], 'On Time',

If([Renewal Quarter 3] < [Renewed On 3], 'Late',

If([Renewal Quarter 3] > [Renewed On 3], 'Early')))) as [Renewal Status]

ahmonah2017
Contributor III
Contributor III
Author

Hi Sunny,

It didn't work. Still getting the same result.

t_chetirbok
Specialist
Specialist

Hi ,

are these fields [Renewal Quarter 3] and [Renewed On 3] dual or just text?

MarcoWedel

please post some sample data meeting each of your conditions and your expected result.

thanks

regards

Marco

sunny_talwar

Can you test just this to see if it works or not

If([Renewal Quarter 3] = 'Not Renewed', 'IT WORKS', 'IT DOESN'T WORK') as Test

This should at least help us understand if the first part of the if statement is working or not

ahmonah2017
Contributor III
Contributor III
Author

It didn't work. The expression updated IT DOESN'T WORK in all cells.

ahmonah2017
Contributor III
Contributor III
Author

Hi Tatsiana,

The data values for both Renewal Quarter 3 & Renewed on 3 is a date transformed into Quarter format.

Ex:

   

Renewal Quarter 32017-Q2
ahmonah2017
Contributor III
Contributor III
Author

Hi Marco,

Sample data shown below:

   

Renewal Quarter 3Renewed On 3Renewal Status
2017-Q22016-Q3Early
2016-Q12016-Q3Late
2016-Q12016-Q3Late
2016-Q12016-Q3Late
2016-Q32016-Q3On Time
2016-Q42016-Q3Early
sunny_talwar

Somethins is not right about [Renewal Quarter 3] then... can you check this


If(WildMatch([Renewal Quarter 3], '*Not*Renewed*', 'IT WORKS', 'IT DOESN'T WORK') as Test