Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
has75042
New Contributor II

Joining dates

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,

1 Solution

Accepted Solutions
pradosh_thakur
Honored Contributor II

Re: Joining dates

Hi

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

Learning never stops.
3 Replies
tincholiver
Contributor

Re: Joining dates

Hey boddy, try this:

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

Re: Joining dates

Hi

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

Learning never stops.
Marcos_rv
New Contributor II

Re: Joining dates


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!!!!

Community Browser