Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
YD
Partner - Contributor II
Partner - Contributor II

Transform severals "same" dates on an unique date

Hello All Qlikview and Sense users,

Could you please help me with issue? Here is my problem:

I have a date field with hours and minutes with severals transactions per minute, for example:

DATE_WITH_HOUR - TRANSACTIONS 

18/06/2019 11:00 -  Transaction 1 

18/06/2019 11:01 - Transaction 2

18/06/2019 11:02 - Transaction 

19/06/2019 11:00 -  Transaction 1

19/06/2019 11:01 - Transaction 2

19/06/2019 11:02 - Transaction 3 

Etc....

I have create another field to create a calendar: 

DATE(DATE#(DATE_WITH_HOUR, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY') AS DATE_WITHOUT_HOUR

18/06/2019

18/06/2019

18/06/2019

19/06/2019

19/06/2019

19/06/2019

Etc.....

-----------------------------------------------------------------------------------------------------------------------------

I would like to have only ONE Date for the same day in the new field, then I can create the calendar and when I choose one date, I shall have all the transactions on that day

Here is the result I am looking for:

DATE WITHOUT HOUR  - DATE WITH HOUR  - TRANSACTION
18/06/2019                            18/06/2019 11:01        Transaction 1
                                                    18/06/2019 11:02      Transaction 2
                                                   18/06/2019 11:03        Transaction 3


19/06/2019                            19/06/2019 11:01       Transaction 1
                                                   19/06/2019 11:02       Transaction 2
                                                   19/06/2019 11:03        Transaction 3


Etc.....

 

-----------------------------------------------------------------------------------------------------------

Thank your very much for your kind help

 

Dimitry

1 Solution

Accepted Solutions
YD
Partner - Contributor II
Partner - Contributor II
Author

Hello Felipe,

 

Thank you very for giving me the lead to the solution, the right function was FLOOR.

But the result was the date as number like this 41852 (Date in number format)

Here is the whole function:

LOAD

DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm' ) AS %DATE,
//DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY') AS CALENDAR_DATE,
DATE(FLOOR(DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY' )), 'DD/MM/YYYY') AS CALENDAR_DATE

FROM .......path

 

So thank you for your kind help

View solution in original post

2 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

They are indeed still different dates, since you didn't "cut" the time part of it, you've just formated it out of the final field, but the time portion is still there.

 

One solution is to round the date with:

floor(DATE_WITH_HOUR)

so every hour of day 18/06/2019 goes to one equal day.

 

Felipe.

YD
Partner - Contributor II
Partner - Contributor II
Author

Hello Felipe,

 

Thank you very for giving me the lead to the solution, the right function was FLOOR.

But the result was the date as number like this 41852 (Date in number format)

Here is the whole function:

LOAD

DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm' ) AS %DATE,
//DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY') AS CALENDAR_DATE,
DATE(FLOOR(DATE(DATE#(%DATE,'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY' )), 'DD/MM/YYYY') AS CALENDAR_DATE

FROM .......path

 

So thank you for your kind help