topic Re: Resolving syntax in script for '=, <,>,-=' in QlikView

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

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

sunny_talwar — Tue, 19 Dec 2017 19:36:01 GMT

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]

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

Anonymous — Tue, 19 Dec 2017 21:21:04 GMT

Hi Sunny,

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

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

t_chetirbok — Tue, 19 Dec 2017 21:46:44 GMT

Hi ,

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

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

MarcoWedel — Tue, 19 Dec 2017 21:53:14 GMT

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

thanks

regards

Marco

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

sunny_talwar — Tue, 19 Dec 2017 21:56:50 GMT

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

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

Anonymous — Tue, 19 Dec 2017 22:16:22 GMT

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

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

Anonymous — Tue, 19 Dec 2017 22:19:36 GMT

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

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

Anonymous — Tue, 19 Dec 2017 22:20:30 GMT

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

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

sunny_talwar — Tue, 19 Dec 2017 22:26:42 GMT

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

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

sunny_talwar — Tue, 19 Dec 2017 22:27:23 GMT

You didn't add a case where you would get ' '... it might help to add that case also

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

MarcoWedel — Tue, 19 Dec 2017 22:32:42 GMT

what about the 'Not Renewed' and blank values?

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

Anonymous — Tue, 19 Dec 2017 22:46:19 GMT

The data below shows Renewal Status incorrectly labeled as Late. The goal is to reflect any row that has not been renewed to stay blank or marked as Not Renewed as well.

Renewal Quarter 3	Renewed On 3	Renewal Status
2014-Q3	Not Renewed	Late
2014-Q3	Not Renewed	Late
2015-Q1	Not Renewed	Late
2015-Q1	Not Renewed	Late
2015-Q1	Not Renewed	Late
2014-Q3	Not Renewed	Late

Renewal quarter 3 is derived from another date in the same row.

Formula below:

If(len([End Date])>0, Year([End Date])&'-'&'Q'& Ceil(Month([End Date])/3),'') as [Renewal Quarter 3]

Renewed On 3 is derived from a mapping function:

Formula below:

if(len(ApplyMap('Key_Table',[EK1],''))>0,Year(ApplyMap('Key_Table',[EK1],''))&'-'&'Q'& Ceil(Month(ApplyMap('Key_Table',[EK1],''))/3),'Not Renewed') as [Renewed On 3],

Hope this helps.

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

t_chetirbok — Wed, 20 Dec 2017 08:07:56 GMT

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

]

;

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

Gysbert_Wassenaar — Wed, 20 Dec 2017 08:47:54 GMT

Make sure all the values in your Date fields are really, really date values and not strings that merely look like dates.

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

Anonymous — Wed, 20 Dec 2017 17:52:09 GMT

Hi Gysbert,

Not sure whether the formula below is turning the value into a string:

If(len([End Date])>0, Year([End Date])&'-'&'Q'& Ceil(Month([End Date])/3),'') as [Renewal Quarter 3],

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

Anonymous — Wed, 20 Dec 2017 17:59:01 GMT

Tatsiana!!! You are awesome!!

Thank you it worked!!!

Sharing a virtual cup of coffee with you at the moment!

Cheers and have an awesome Wednesday!

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

Gysbert_Wassenaar — Thu, 21 Dec 2017 06:52:28 GMT

Yes, that expression returns strings.