Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MadiF
Contributor III
Contributor III

Compare a date field to a variable to generate flag

Goal: Create a 'is in current month' flag for some data.

Issue: calculation I'm using either returns nothing, returns only one of the two conditions of my if statement, or tells me I'm missing a parentheses, even though I'm not. Interestingly,  I found a qlikview post with the same exact issue from 2013 that was never solved, here: https://community.qlik.com/t5/QlikView-App-Development/Compare-dates-in-Load-Script/td-p/451152#

The DateField I want to create the flag for has the format:  M/D/YYYY

The variable I want to compare to has the format: M/D/YYYY (actual value: 9/1/2020)

     How I formed that variable: Let vMaxDate = peek('maxdate') -------- the format of 'maxdate' is also M/D/YYYY

My attempt at creating a flag:  if(DateField>= '$(vmaxdate)', 'Y', 'N') as CurrentMonthFlag  ----- which in the front end just returns 'Y' for everything, I assume because it's not actually reading the variable correctly, similar to what the user described in the post I linked above.

Note: I've tried to alter the formats within the flag in many different ways (wrapping things in 'date' function, 'num' function, using or not using the single quotes $() v '$()', just in case differing formats was the issue) and nothing has worked.

Labels (1)
5 Replies
tlamont
Partner - Contributor II
Partner - Contributor II

Is the flag being create in the script?  If so, when you run the script what is the value for the vMaxDate variable? 

QFabian
Specialist III
Specialist III

Hi @MadiF ,

you have to convert your dates to numbers from the begining, as you said, using num(YourDate) when you load your data from youir initial source.

Data:

load

fields,

num(Date) as Num

from OriginalSource;

In that way, when you use that field in your expressions for comparison, as they are numbers, you dont need to format anything.

load

GroupingIdField,

num (max (date))  as maxdate

Resident Data

group by 

GroupingIdField;

Check how your dates are from source, because you talk about ' M/D/YYYY', and maybe your defined format is 'M-D-YYYY' :
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=1;

 

QFabian
MadiF
Contributor III
Contributor III
Author

Yes I'm trying to create this in the script- the variable returns 9/1/2020 and it's available in the front end for use so the variable itself shouldn't be the problem

MadiF
Contributor III
Contributor III
Author

I've tried wrapping everything in num() in various ways and it hasn't worked/solved the issue, unfortunately. Ex:

1)  if(num(DateField)>= '$(vmaxdate)', 'Y', 'N') as CurrentMonthFlag

2)  if(DateField>= num('$(vmaxdate)'), 'Y', 'N') as CurrentMonthFlag

3)  if(num(DateField)>= num('$(vmaxdate)'), 'Y', 'N') as CurrentMonthFlag

4) wrapping the OG variable in 'num' in the set statement

-All didn't work

A7R3
Contributor III
Contributor III

This works for me. You may want to give it a try.

 

InMonthToDate(TempDate, maxdate, 0) * -1 as _CMTD_Flag,
InMonthToDate(TempDate, maxdate, -12) * -1 as _PMTD_Flag,
InYearToDate(TempDate, maxdate, 0, 11) * -1 as _CYTD_Flag,
InYearToDate(TempDate, maxdate, -1, 11) * -1 as _PYTD_Flag,