Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
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!

17 Replies
sunny_talwar

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

MarcoWedel

what about the 'Not Renewed' and blank values?

ahmonah2017
Contributor III
Contributor III
Author

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 3Renewed On 3Renewal Status
2014-Q3Not RenewedLate
2014-Q3Not RenewedLate
2015-Q1Not RenewedLate
2015-Q1Not RenewedLate
2015-Q1Not RenewedLate
2014-Q3Not RenewedLate

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.

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
ahmonah2017
Contributor III
Contributor III
Author

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],

ahmonah2017
Contributor III
Contributor III
Author

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!

Gysbert_Wassenaar

Yes, that expression returns strings.


talk is cheap, supply exceeds demand