Compare dates in Load Script

[#1]

This seems entirely simple, but has been kicking my rear for several hours now - hoping someone can help.

I'm working with a table called "Employee Labor Info" that contains all of the labor rate info associated with employees (i.e. wages, salaries, titles, etc).  I have a field in the table labeled [End Date] that represents the date on which an employee's status changed (e.g. if transitioning from hourly wages to salary, the hourly line associated with the employee's ID field would have an end date; or if the wage rate increased, then there would be an end date for the previous rate, and a new line for the employee to reflect the current status).  By default, this field is set to a date ridiculously far in the future (12/31/2078) until there is a change in status, at which point the date is set to the status change date, and the new line representing the employee's new status is created and set to the 12/31/2078 date)

All I want to do is create a flag in the script load to indicate whether a given line in this table is "Active" or "Inactive".  In pseudocode, the logic is:

IF [End Date] is less than or equal to "Date at which data is loaded",
     THEN set flag to "Inactive"
     ELSE set flag to "Active"

I want to do this in the script load in order to make things more efficient.

I have a script variable "vRefreshTime" that captures the timestamp of the QVD source data.  This script variable is passed into the application after the load.

LET vRefreshTime = QvdCreateTime ('[data path string here]');

After loading data in and experimenting, I've managed to make the logic of the formula work in the front QVW as follows:

If (
     Floor([End Date]) <= Floor(date(vRefreshTime, TimeStampFormat)),
     , 'Inactive'
     , 'Active'
     )

This correctly sets the flag.  I then attempted to recreate this in the load script with the same formula, but I'm getting errors that I can't figure out.

In the script, I wrote the following code as the last line in the load script for the [Employee Labor Info] table:

(If(Floor([End Date]) <= Floor(date($(vRefreshTime), TimeStampFormat)), 'Inactive', 'Active')) as [Employee Labor Status]

However, every time I run it, I get an error message:  "Error in expression: ')' expected".  I've gone crazy trying to figure this out.  The error message in the loadscript shows the variable vRefreshTime has been expanded (i.e. there's a valid date-time entry showing in the message at its location in the code).

It seems like Qlikview handles dates differently in the script than in the application, but I can't for the life of me figure out how to deal with it.

[kaushiknsolanki]

Try this,

If(Floor([End Date]) <= Floor(date($(vRefreshTime), $(TimeStampFormat))), 'Inactive', 'Active') as [Employee Labor Status]

The only change is when you are using the variable in script, it should be called (used) within $( ), so in your case the variable "TimeStampFormat" should be used as $(TimeStampFormat).

Hope this is clear.

Regards,

Kaushik Solanki

[#3]

Thanks for the suggestion - but that still doesn't work.  I've done some more experimenting, and it seems like the issue is the vRefreshTime variable simply isn't getting expanded in the script for whatever reason.  And there's something funky to do with syntax related to the 'IF' statement that I can't figure out.

I tried the variable expansion as you suggested and still get the error message relating to a missing ')'.  Here's the error message:

Error in expression:
')' expected
[Employee Labor Info]:
LOAD [Empl ID]
          , [Hourly or Salary?]
          , Exempt?
          , [Empl Type]
          , [Empl Org ID]
          , [Work State]
          , [Lab Loc Cd]
          , [Empl Class Cd]
          , [Eff Hire Dt?]
          , [Eff Term Dt?]
          , Date(Floor([End Dt]),'YYYY-MM-DD') as [Empl Status End Date]
          , If(Floor([End Dt]) <= Floor(date(, MM/DD/YYYY hh:mm:ss[.fff] TT)), 'Inactive', 'Active') as [Empl Lab Status]
FROM
[..\Data\QVDs\EmplLabInfo.qvd]
(qvd)
WHERE NOT WildMatch([Empl ID], '99*');

I've counted parentheses till my eyes bleed, can't figure out where that might be the issue.  Note after the "date" function there's a blank - it doesn't appear that QV is expanding the variable in the script.

I then tried to use the vRefreshTime variable to set another variable, vCompare, as follows:

LET vRefreshTime = QvdCreateTime ('$(vFolderSourceData)GLPostSum.qvd');
LET vCompareDate = Floor($(vRefreshTime));

This appears to work fine.  So I then used the vCompareDate variable in the 'If' statement in my load script - thinking here is that maybe it would have an easier time if we cut out a step in the load script.  Still no success with this code, which replaced the last line before the "FROM" statement in the statement above:

If([Empl Status End Date] <= ($(vCompareDate)), 'Inactive', 'Active') as [Empl Labor Status]

Got the same error expression:

Error in expression:
')' expected
[Employee Labor Info]:
LOAD [Empl ID]
          , [Hourly or Salary?]
          , Exempt?
          , [Empl Type]
          , [Empl Org ID]
          , [Work State]
          , [Lab Loc Cd]
          , [Empl Class Cd]
          , [Eff Hire Dt?]
          , [Eff Term Dt?]
          , Date(Floor([End Dt]),'YYYY-MM-DD') as [Empl Status End Date]
          , If([Empl Status End Date] <= (), 'Inactive', 'Active') as [Empl Labor Status]
FROM
[..\Data\QVDs\EmplLabInfo.qvd]
(qvd)
WHERE NOT WildMatch([Empl ID], '99*');

notice again how the comparison in the IF test isn't expanded?  "($(vCompareDate))" resolves to "()" at runtime.  I've beaten my head in trying to understand what's going on here...

[Larry_Aaron]

I started reading your script and notice your where clause.  WildMatch will return an integer of 1 if it finds '99*' in the field [Empl ID] so I would write it

WHERE WildMatch([Empl ID], '99*')=0;

NOT

>>WHERE NOT WildMatch([Empl ID], '99*');

[kaushiknsolanki]

Hi,

     Try this.

     If([Empl Status End Date] <= $(vRefreshTime), 'Inactive', 'Active') as [Empl Labor Status];

     Dont use the floor and try.

Regards,

Kaushik Solanki

[Larry_Aaron]

I am able to get your concept working using some test data.  My souce data for the EndDt column is a string in the format of 01/01/2014. 

let  vRefreshTime = Floor(QvdCreateTime('myTestQVD.qvd')); 

if(Floor(date#([End Dt])) <= '$(vRefreshTime)', 'Inactive', 'Active') as [Employee Labor Status]

[#7]

I have several rows of junk data in the table with an EMPL_ID that begins with "99" - e.g. "99901".  The Where Not clause is designed to screen these out of the load.  It seems to be working as designed as these rows (4 or 5 of them) aren't getting loaded into the app.

[#8]

Thanks, but I'm still getting the error:

Error in expression:
')' expected
[Employee Labor Info]:
LOAD [Empl ID]
          , [Hourly or Salary?]
          , Exempt?
          , [Empl Type]
          , [Empl Org ID]
          , [Work State]
          , [Lab Loc Cd]
          , [Empl Class Cd]
          , [Eff Hire Dt?]
          , [Eff Term Dt?]
          , Date(Floor([End Dt]),'YYYY-MM-DD') as [Empl Status End Date]
          , If([Empl Status End Date] <= 2013-04-13 09:10:05, 'Inactive', 'Active') as [Empl Labor Status]

Note that in the line before yours, I'd already built a function to make sure the formatting for [End Dt] (the original date that is in the source table) was correct, and renamed it as [Empl Status End Date].  I know this function worked b/c I've already run it in my code successfully.  I substituted [Empl Status End Date] in the IF expression on the last line - so that the test was being run on the original source data - and got the exact same error message as this.

I did manage to get what I wanted, though it took some workarounds and I still don't know why the date comparisons in the script aren't functioning.  Here's how I did it, in case anyone else runs into a similar situation:

1.  First, I set a comparison variable in the script to transform vRefreshTime into a number:

LET vComparePeriod = (
          (Num(Year(vRefreshTime)) * 10000) 
          + (Num(Month(vRefreshTime)) * 100)
          + (Num(Day(vRefreshTime)))
          );

2.  Then, in the load script, I did the same transformation to the target date from the source data and made the comparison based on that:

, If(
     ((Num(Year([End Dt])) * 10000) 
     + (Num(Month([End Dt])) * 100)
     + (Day([End Dt]))) <= $(vComparePeriod)
     , 'Inactive'
     , 'Active'
     ) as [Empl Lab Line Status]

This ultimately got me what I wanted, though it seems like a big workaround.  I'd still like to know why I can't manage to get the date comparisons to work in my script.  I've tried tons of variations, attempting to make sure that I transformed all of the data into the same format, etc.  For example:

If(Date([End Dt]) <= $(vRefreshTime), 'Inactive', 'Active') as [Test Status] 

...this still generates the "Error in expression: ')' expected" message indicated above.  The $(vRefreshTime) variable does expand - it just doesn't get compared to the [End Dt] field.

[swuehl]

I then tried to use the vRefreshTime variable to set another variable, vCompare, as follows:

LET vRefreshTime = QvdCreateTime ('$(vFolderSourceData)GLPostSum.qvd');
LET vCompareDate = Floor($(vRefreshTime));

This appears to work fine.  So I then used the vCompareDate variable in the 'If' statement in my load script - thinking here is that maybe it would have an easier time if we cut out a step in the load script. 

If you say, this works fine, how do you tell? Have you checked the result / content of vCompareDate by e.g. using a TRACE? Or using the debug mode and stepping through your code?

I assume your vRefreshTime holds a string (the result of QvdCreateTime() ) and applying floor() to a string returns NULL. A variable won't handle NULL like a field, so expanding your variable might show just NOTHING (something like you see).

Try using

LET vRefreshTime = floor(QvdCreateTime ('$(vFolderSourceData)GLPostSum.qvd'));

e.g. use the numeric value already in your initial variable definition.

As a general advice, try using TRACE statements or debug mode to look at your variable values for debug purposes.

Hope this helps,

Stefan

P.S: another issues: Looking at your OP, your TimeStampFormat isn't expanded correctly

a) use dollar sign expansion like already suggested above

b) Take care that your expanded variable value will be interpreted as string by QV. In you example, you would need to add two single quotes around:

'$(TimeStampFormat)'

(if you use the standard timestamp format already set at the front of the script, you can leave out the format code argument).