topic Re: Transform severals "same" dates on an unique date in QlikView

Transform severals "same" dates on an unique date

YD — Tue, 18 Jun 2019 13:05:05 GMT

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

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

Re: Transform severals "same" dates on an unique date

felipedl — Tue, 18 Jun 2019 14:40:23 GMT

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.

Re: Transform severals "same" dates on an unique date

YD — Wed, 19 Jun 2019 11:59:00 GMT

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