Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

nickedw0
New Contributor III

Unexpected Date compare results

I am getting following results from the following script. Since 04/02/2017 (row1) is less than May1 (from PriorMonthStart) I expect that _Period1_Flag should be equal to 1 and not 0

LET cSignificanceInScopeMedium='MEDIUM';
Let vToDate = Today();
Set vCurrentMonthStart ='=MonthStart(vToDate,0)';

Set vPriorMonthStart = '=MonthStart(ToDay(),-1)';

Table1:
 
Load * Inline
[LoanSystemDate, LOANNUMBER,Significance, _ExceptionInScope_Flag
04/02/2017,123456,MEDIUM,1
05/02/2017,234567,HIGH,1
06/01/2017,345678,MEDIUM,1
06/25/2017,345678,MEDIUM,1
07/25/2017,345678,MEDIUM,1
07/20/2017,345678,MEDIUM,1
]
;
 
Table2:
 
Load *
,
If( LoanSystemDate < '$(vPriorMonthStart)', 1,0)
As _Period1_Flag

,
If(LoanSystemDate >= '$(vPriorMonthStart)', 1,0)
As _Period2_Flag

,
If(LoanSystemDate >= '$(vCurrentMonthStart)', 1,0)
As _Period3_Flag

Resident Table1;
Drop Table Table1;

Variables created by script look like this:

1 Solution

Accepted Solutions
nickedw0
New Contributor III

Re: Unexpected Date compare results

Soluion lay in using Let instead of Set:

Let vToday = Today();
Let vToDate = Date(Today());

Let vCurrentMonthStartMonthStart(ToDay(),0);
Let vPriorMonthStart  =  MonthStart(ToDay(),-1);

Let vPriorWeekStart = WeekStart(ToDay(),-1);
Let vPriorFriday  = DayEnd(WeekStart(ToDay(),-1),4);

Table1:
Load * Inline
[LoanSystemDate, LOANNUMBER,Significance, _ExceptionInScope_Flag
04/02/2017,123456,MEDIUM,1
05/02/2017,234567,HIGH,1
05/02/2017,345678,MEDIUM,1
06/01/2017,456789,MEDIUM,1
06/25/2017,545678,MEDIUM,1
07/25/2017,645678,MEDIUM,1
07/20/2017,745678,MEDIUM,1
]
;


Table2:
Load *
,
If( LoanSystemDate < '$(vPriorMonthStart)', 1,0)
As _letPeriod1_Flag 

,
If(LoanSystemDate >= '$(vPriorMonthStart)', 1,0)
As _letPeriod2_Flag

,
If(LoanSystemDate >= '$(vCurrentMonthStart)', 1,0)
As _letPeriod3_Flag


Resident Table1;
Drop Table Table1;

1 Reply
nickedw0
New Contributor III

Re: Unexpected Date compare results

Soluion lay in using Let instead of Set:

Let vToday = Today();
Let vToDate = Date(Today());

Let vCurrentMonthStartMonthStart(ToDay(),0);
Let vPriorMonthStart  =  MonthStart(ToDay(),-1);

Let vPriorWeekStart = WeekStart(ToDay(),-1);
Let vPriorFriday  = DayEnd(WeekStart(ToDay(),-1),4);

Table1:
Load * Inline
[LoanSystemDate, LOANNUMBER,Significance, _ExceptionInScope_Flag
04/02/2017,123456,MEDIUM,1
05/02/2017,234567,HIGH,1
05/02/2017,345678,MEDIUM,1
06/01/2017,456789,MEDIUM,1
06/25/2017,545678,MEDIUM,1
07/25/2017,645678,MEDIUM,1
07/20/2017,745678,MEDIUM,1
]
;


Table2:
Load *
,
If( LoanSystemDate < '$(vPriorMonthStart)', 1,0)
As _letPeriod1_Flag 

,
If(LoanSystemDate >= '$(vPriorMonthStart)', 1,0)
As _letPeriod2_Flag

,
If(LoanSystemDate >= '$(vCurrentMonthStart)', 1,0)
As _letPeriod3_Flag


Resident Table1;
Drop Table Table1;

Community Browser