Solved: Re: Resolving syntax in script for '=, <,>,-=' - Qlik Community

Anonymous · ‎2017-12-19

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.

Any help would be appreciated!

Thanks!

t_chetirbok · ‎2017-12-20

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-Q3, 2016-Q3

2016-Q4, 2016-Q3

]

;

View solution in original post

sunny_talwar · ‎2017-12-19

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]

Anonymous · ‎2017-12-19

Hi Sunny,

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

t_chetirbok · ‎2017-12-19

Hi ,

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

MarcoWedel · ‎2017-12-19

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

thanks

regards

Marco

sunny_talwar · ‎2017-12-19

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

Anonymous · ‎2017-12-19

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

Anonymous · ‎2017-12-19

Hi Tatsiana,

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

Ex:

Renewal Quarter 3	2017-Q2

Anonymous · ‎2017-12-19

Hi Marco,

Sample data shown below:

Renewal Quarter 3	Renewed On 3	Renewal Status
2017-Q2	2016-Q3	Early
2016-Q1	2016-Q3	Late
2016-Q1	2016-Q3	Late
2016-Q1	2016-Q3	Late
2016-Q3	2016-Q3	On Time
2016-Q4	2016-Q3	Early

sunny_talwar · ‎2017-12-19

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