Datefield

[woody1982]

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

[Anil_Babu_Samineni]

@woody1982 Try with TimeStamp(), more can found here: https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/Scripting/NumberInte... 

[woody1982]

@Anil_Babu_Samineni 

Good morning
Unfortunately nothing works

Timestamp( ATFLV,'YYYY-MM-DD hh.mm')

[steeefan]

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:

[woody1982]

Unfortunately this does not work.
The ATFLV field has several such values, not just the one

[steeefan]

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.

[woody1982]

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

[steeefan]

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?

[woody1982]

---

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

[steeefan]

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?