Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Confusion about Interpretation Functions (Date or Date# / Num or Num# / Time or Time#)

Hello !

On our installation we grab all data from CSV files which are downloaded from Mainframes plain text files.

Since we dont trust so much on the field contents, we want our scripts to TEST all of them during load process.

The general idea is this :

  • Any blank field, either string, date or number, should be loaded as blank, that is '' (empty string);
  • Any INVALID field, such as an invalid date (out of calendar, with letters inside, not well formatted etc) should be loaded with the word INVALID, disregarding original damaged content. Same concept should be used for numeric fields or time fields. Also for Money fields. So, any invalid number should have INVALID as field content.

Here's how we programmed our script :

LOAD
*
,IF(DATE_TEST = '','',ALT(DATE (DATE_TEST,'DD/MM/YYYY'),'INVALID DATE' )) AS DATE_DER_TEST
,IF(DATE_TEST = '','',ALT(DATE# (DATE_TEST,'DD/MM/YYYY'),'INVALID DATE' )) AS DATE#_DER_TEST
,IF(TIME_TEST = '','',ALT(TIME (TIME_TEST,'HH:MM:SS' ),'INVALID TIME' )) AS TIME_DER_TEST
,IF(TIME_TEST = '','',ALT(TIME# (TIME_TEST,'HH:MM:SS' ),'INVALID TIME' )) AS TIME#_DER_TEST
,IF(NUM_TEST = '','',ALT(NUM (NUM_TEST ,'#' ),'INVALID NUMBER')) AS NUM_DER_TEST
,IF(NUM_TEST = '','',ALT(NUM# (NUM_TEST ,'#' ),'INVALID NUMBER')) AS NUM#_DER_TEST
,IF(MONEY_TEST = '','',ALT(MONEY (MONEY_TEST,'#' ),'INVALID MONEY' )) AS MONEY_DER_TEST
,IF(MONEY_TEST = '','',ALT(MONEY#(MONEY_TEST,'#' ),'INVALID MONEY' )) AS MONEY#_DER_TEST
;

However, I'm not sure if I should use NUM or NUM# . Also confused about DATE and DATE#.

We also need to be aware that the loaded fields should jump perfectly fine into Excel. So, Date fields, when trhown into Excel, should bubble up OK right in there. About this issue DATE and DATE# are different, even though on QV itself the dates appear to be OK.

Anwyay, I guess it's easier for you to understand if you load the prototype attached application.

Thanks for you support !

0 Replies