- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Datefield
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Now we have the solution:
Date(Date#(num(num(replace([ATFLV],'.',','),'(dez)'),'00000000'),'YYYYMMDD'))
Thanks all for the help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@woody1982 Try with TimeStamp(), more can found here: https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/Scripting/NumberInte...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unfortunately this does not work.
The ATFLV field has several such values, not just the one
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- « Previous Replies
-
- 1
- 2
- Next Replies »