Issue with date format

[Daryn]

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

[vincent_ardiet_ · Accepted solution]

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

[Daryn · Accepted solution]

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 👍