Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

Highlighted

what about the 'Not Renewed' and blank values?

Highlighted
Contributor III
Contributor III

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.

Highlighted
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

Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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!

Highlighted
MVP & Luminary
MVP & Luminary

Yes, that expression returns strings.


talk is cheap, supply exceeds demand