topic Re: Joining dates in QlikView

Joining dates

has75042 — Wed, 25 Nov 2020 16:16:04 GMT

Hey All,

I need to find out how many orders were place FY 2019. We have a calendar dim, but somehow I can't connect my source table to calendar dim.

I have changed the table date format to match the calendar dim format, but I still can't get FY2019 field to work.

Source Table Date Format :

4/1/2018 12:00:00 AM

Calendar Dim Date Format:

2/2/2012

the below expression I am using to change the date format

date( `time_res`, 'MM/DD/YYYY') as time_res,

Any help would be really appreciated

Thanks,

tincholiver — Mon, 11 Feb 2019 17:09:22 GMT

Hey boddy, try this:

Date(Floor(Date#(date, 'D/M/YYYY h:m:ss')), 'DD/MM/YYYY') as time_res;

pradosh_thakur — Mon, 11 Feb 2019 17:17:09 GMT

the first thing you need to check is your time stamp and date are in date/time format or string?

if they are in timestamp/date format then

for source table date(floor(timestamp_field)) will do

if they are in string then you need to first change them to timestamp then change it to date

date(floor(timestamp#(timestamp_field,'MM/DD/YYYY hh:mm:ss tt')))

In the above i have assumed your dates are in MM/DD/YYYY format, you may nee to change that if otherwise.

Thanks

Pradosh

Marcos_rv — Mon, 11 Feb 2019 20:16:21 GMT

yep, as it says tincholiver, you have to use the function floor to make join between dates.

This is because in qv the dates are a number and the date format is a mask, for example:

date (40032.5123, 'dd-mm-yyyy hh: mm: ss')
= 07-17-2009 12:17:42

The numbers that are after the period (.) Represent the hours, minutes and seconds.

So if you had to relate 2 formats, only with the function date could not, since it is a mask, you would have to extract the decimals.

example: date (floor (40032.5123)) = 07-17-2009

regards!!!!