Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would like to analyse the ATFLV field from the SAP table AUSP.
However, the date entered is in the format "2.0230502000000000000E+007".
How can I convert this into a correct date?
Thank you very much
Now we have the solution:
Date(Date#(num(num(replace([ATFLV],'.',','),'(dez)'),'00000000'),'YYYYMMDD'))
Thanks all for the help!
@woody1982 Try with TimeStamp(), more can found here: https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/Scripting/NumberInte...
If the value from AUSP.ATFLV is read into Qlik Sense as "2.0230502000000000000E+007" and that then should mean 2023-05-02, my suggestion is:
Data:
NOCONCATENATE LOAD
*,
Date(Date#(Left(ATFLV, 9) * pow(10, 7), 'YYYYMMDD'), 'YYYY-MM-DD') AS ATFLVDate;
LOAD * INLINE [
ATFLV
2.0230502000000000000E+007
];
This would give you:
Unfortunately this does not work.
The ATFLV field has several such values, not just the one
What do you mean?
This was just an example based on the value you posted. If all the values in AUSP.ATFLV are formatted the same way, which they most likely will be, then the transformation I posted will work:
Date(Date#(Left(ATFLV, 9) * pow(10, 7), 'YYYYMMDD'), 'YYYY-MM-DD') AS ATFLVDate
I only used LOAD * INLINE to get that specific value into my Qlik script.
Ah okay, I understand 🙂
But unfortunately it still doesn't work
Script:
Date(Date#(Left(ATFLV, 9) * pow(10, 7), 'YYYYMMDD'), 'YYYY-MM-DD') AS ATFLVDate
the result see screenshot
All I can say is: Works for me for any value that is formatted as specified.
Data:
NOCONCATENATE LOAD
*,
Date(Date#(Left(ATFLV, 9) * pow(10, 7), 'YYYYMMDD'), 'YYYY-MM-DD') AS ATFLVDate;
LOAD * INLINE [
ATFLV
2.0230502000000000000E+007
2.0230227000000000000E+007
2.0230101000000000000E+007
1.9700101000000000000E+007
2.0200314000000000000E+007
];
Could you share the script you are using and ideally an export of some of the ATFLV values?
SET ThousandSep='.'; SET DecimalSep=','; SET MoneyThousandSep='.'; SET MoneyDecimalSep=','; SET MoneyFormat='#.##0,00 €;-#.##0,00 €'; SET TimeFormat='hh:mm:ss'; SET DateFormat='YYYY-MM-DD'; SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]'; SET FirstWeekDay=0; SET BrokenWeeks=0; SET ReferenceDay=4; SET FirstMonthOfYear=1; SET CollationLocale='de-DE'; SET CreateSearchIndexOnReload=0; SET MonthNames='Jan.;Feb.;März;Apr.;Mai;Juni;Juli;Aug.;Sep.;Okt.;Nov.;Dez.'; SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember'; SET DayNames='Mo.;Di.;Mi.;Do.;Fr.;Sa.;So.'; SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag'; SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y'; set vReplaceLeading0 = replace(ltrim(replace([$1], '0', ' ')), ' ', 0); LOAD $(vReplaceLeading0(OBJEK)) as OBJEK, ATINN as %Key_ATINN, KLART as %Key_KLART, ATINN, KLART, ATWRT, ATFLV, Date(Date#(Left(ATFLV, 9) * pow(10, 7), 'YYYYMMDD'), 'YYYY-MM-DD') AS ATFLVDate FROM [lib://Purchase:DataFiles/0010_QVD_Raw_Z_ERP_NEW_AUSP.qvd] (qvd) where OBJEK > 000700000 and OBJEK < 0007999999; Exit Script
Here is the Script and the download of ATFLV
Still works for me:
ATFLV:
NOCONCATENATE LOAD
ATFLV,
Date(Date#(Left(ATFLV, 9) * pow(10, 7), 'YYYYMMDD'), 'YYYY-MM-DD') AS ATFLVDate
FROM
[lib://00 Files/Debug/ATFLV.xlsx]
(ooxml, embedded labels, table is Tabelle1);
Just to be sure, I also stored these values as a QVD file. When opening it in a QVD explorer, the values are shown as expected, i.e. 2.02212310000000000E+007. But when previewing the same QVD in Qlik Sense, it shows something else:
When loaded from QVD, the format is apparently different. Maybe it's only because I created the file myself from your Excel export. Can you verify this?