topic Re: Datefield in App Development

Datefield

woody1982 — Mon, 22 Jan 2024 13:09:53 GMT

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

Re: Datefield

Anil_Babu_Samineni — Mon, 22 Jan 2024 14:27:30 GMT

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

Re: Datefield

woody1982 — Tue, 23 Jan 2024 05:15:27 GMT

@Anil_Babu_Samineni

Good morning
Unfortunately nothing works

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

Re: Datefield

steeefan — Tue, 23 Jan 2024 06:03:27 GMT

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:

Re: Datefield

woody1982 — Tue, 23 Jan 2024 08:30:17 GMT

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

Re: Datefield

steeefan — Tue, 23 Jan 2024 08:34:09 GMT

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.

Re: Datefield

woody1982 — Tue, 23 Jan 2024 08:51:10 GMT

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

Re: Datefield

steeefan — Tue, 23 Jan 2024 08:57:43 GMT

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?

Re: Datefield

woody1982 — Tue, 23 Jan 2024 09:10:49 GMT

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

Re: Datefield

steeefan — Tue, 23 Jan 2024 09:25:42 GMT

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?

Re: Datefield

woody1982 — Wed, 24 Jan 2024 06:14:17 GMT

Sorry, I'm still an absolute beginner 🙂 I can't understand that

Re: Datefield

steeefan — Wed, 24 Jan 2024 07:16:04 GMT

In the QS data load editor, navigate to your library, then open it and select the QVD file you want to get the data from:

On the right under "Data connections", select "Purchase:DataFiles"
In there, select "0010_QVD_Raw_Z_ERP_NEW_AUSP.qvd"
Click "Select"
In the preview window, locate the column ATFLV and check it the format is the same as the one you specified earlier, i.e. 2.0230502000000000000E+007, or if its different.

In my screenshot from yesterday, I have the data load preview on the left and the QVD opened in an external QVD viewer application on the right:

If the format in the preview window does not match the one you specified, my parsing script will not work and needs to be adapted.

Re: Datefield

woody1982 — Tue, 30 Jan 2024 05:51:07 GMT

Now we have the solution:

Date(Date#(num(num(replace([ATFLV],'.',','),'(dez)'),'00000000'),'YYYYMMDD'))

Thanks all for the help!