Issue with date format

Daryn — Mon, 16 Oct 2023 14:59:18 GMT

Hi,

I have an app that works, loading data in excel format, (from an SAP export). I am now trying to point this at a QVD (that is created using the Qlik/SAP connector). So new field names.

The two date fields created in the second part of the load (DATE281 & DATE501 are not working correctly. The issue appears to be with the format.

In the first part (along with other fields I am loading;

[MSEG]: LOAD Date(BUDAT_MKPF,'DD.MM.YYYY') AS [MSEG BUDAT], BWART FROM [lib://QVD-Generator_Connector/MSEG.qvd] (qvd);

[MSEG BUDAT] appears to be fine and correct;

Then I have;

// USED TO ONLY CALCULATE THE FIRST BOOKING IN (501) DATE AGAINST THE FIRST (281) BOOKED OUT DATE Set dataManagerTables = '','MvT281', 'MvT501'; For each name in $(dataManagerTables) Let index = 0; Let currentName = name; Let tableNumber = TableNumber(name); Let matches = 0; Do while not IsNull(tableNumber) or (index > 0 and matches > 0) index = index + 1; currentName = name & '-' & index; tableNumber = TableNumber(currentName) matches = Match('$(currentName)', $(dataManagerTables)); Loop If index > 0 then Rename Table '$(name)' to '$(currentName)'; EndIf; Next; Set dataManagerTables = ; MvT281_load: LOAD Floor(date([BUDAT_MKPF],'DD.MM.YYYY')) as DATE281, CHARG FROM [lib://QVD-Generator_Connector/MSEG.qvd] (qvd) where BWART = 281; main: NOCONCATENATE LOAD FirstValue (DATE281) AS DATE281, CHARG resident MvT281_load group by CHARG order by DATE281 asc ; drop table MvT281_load; MvT501_load: LOAD Floor(date([BUDAT_MKPF],'DD.MM.YYYY')) AS DATE501, CHARG FROM [lib://QVD-Generator_Connector/MSEG.qvd] (qvd) where BWART = 501; outer join (main) LOAD FirstValue(DATE501) AS DATE501, CHARG resident MvT501_load group by CHARG order by DATE501 asc ; drop table MvT501_load;

But both those date fields appear in the app as if they have not been formatted?

I have tried several option of the formatting (and with date#) but all come back in this wrong (above image) format.

Any help appreciated.

Thanks Daryn

Re: Issue with date format

vincent_ardiet_ — Mon, 16 Oct 2023 16:03:46 GMT

Could you try to put Floor inside the Date function and not outside?

Re: Issue with date format

Daryn — Tue, 17 Oct 2023 07:24:46 GMT

Hi and many thanks for taking the time to respond.

I hadn't tried that, when I moved floor inside it returned blank values, so I removed the formatting and it works.

Originally:

MvT281_load:

LOAD 

Floor(date([BUDAT_MKPF],'DD.MM.YYYY')) as DATE281,

CHARG

Solution:

MvT281_load:
LOAD
date(Floor([BUDAT_MKPF])) as DATE281,
CHARG

Thank you 👍

topic Re: Issue with date format in App Development

Issue with date format

Re: Issue with date format

Re: Issue with date format