Hello all,
During development, i have came across certain issues which are all co-related to Date or Timestamp Formats.
The code which follows can put an end to these kind of glitches in our apps:
Initially by making an excel which should be populated with all occurrences of Date/Timestamp Formats.Then ,by using this excel we will peek and make an Alt Statement conditions for every Date/Timestamp Formats to achieve required formats as output.
Please find the attached QVW Code and excel for ref.
By using this snippet of code :
////////////////////////Snippet Of code////////////////////////////////////////////////////////////////
$(Include=\\Location\first_iterate_code.qvs);//First Iterate code
Let vDate='Date_FOrmat'; //Put Your Date field here in quotes.
$(Include=\\Location\second_iterate_code.qvs);//Second Iterate code
//Put $(vDateTimeFormats) in alt condition like below example for ref and rename it as required.
///////Date or timestamp(alt($(vDateTimeFormats)),'Provide what ever format required') as [What ever required]
////////////////////////Snippet Of code////////////////////////////////////////////////////////////////
Date:
LOAD *,
Date(alt($(vDateTimeFormats)),'DD-MM-YYYY') as Date_Created
INLINE [
Date_FOrmat
12-08-2019 08:09:33
12/08/2019 08:09:33
12-Aug-2019 08:09
1-01-18
1-Jan-18 08:10
1-Jan-18 08:10 PM
01-Feb-2019
];
EXIT SCRIPT;
The above script contains two iterate codes which were loaded using must include into script, there by we can use them anywhere prior to the table which we are trying to fix Date/Time issues. This script will contain nothing but variable which will be changed based on Field provided in vDate.
So in place of vDate , one can use the field name of Date/Time from source table. After that in our Date /time parsing Alt condition use the same $(vDateTimeFormats). So that all the formats which ever loaded into variable will be evaluated there and retrieves the desired output formats.
There by using this snippet of code before the source table which is having multiple Date /Timestamp Formats can help us to achieve desired output format.
Please look into below for alt function information:
https://community.qlik.com/t5/Qlik-Sense-Enterprise-Documents/Alt-Conditional-Function/ta-p/1478846
PFA QVW, 2 QVS script files, Excel for Ref.
Hope this will be helpful for qlikers around.
Best Regards,
Hirish V
