Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a customer purchase information and it looks like this.
NUM NAME DATE
1 David 2015-02-22 12:22:55
2 Hannah 2016-04-12 21:55:14
3 Tanya 2014-05-14 22:10:55
and my date QVD has multiple date information but it has a variable that is
Calendar date Fiscal year.... .... ... ....
1990-01-01
1990-01-02
1990-01-03
......
I am trying to left join my table to Date QVD by joining DATE variable from the table and Calendar date column of the QVD.
so what i did was used the date function to the DATE column and made it to have YYYY-MM-DD information and left joined the QVD.
so
--------------------------------------------------------------------------------------
load...
...
date(DATE) as [Calendar date]
left join (table name)
load
[Calendar Date]
....
from....
---------------------------------------------------------------------------------------------------
but this does not seem to join the two tables the way I want. I am really bad at explaining I guess.
Can please anyone answer why I am not able to join the two quite well?
if anything is not clear please let me know!!
Thank you in advance!!
The Date() function just changes the display format but keeps the decimal time in its numeric format, You need to use th Floor() function for remove the decimal time.
date(Floor(DATE)) as [Calendar date]
Try this:
Load...
...
date(Floor(DATE)) as [Calendar date]
left join (table name)
load
[Calendar Date]
....
from....
You can also try date(DayStart(Date))
Dear David,
Here is a data model,
Table1:
Load Field1,
Field2,
Num(Date(Floor(Timestamp#(YourDateFieldName, 'YYYY-MM-DD hh:mm:ss')), 'DD/MM/YYYY')) as Date
From YourTable;
Left Join (Table1)
Load Field3,
Num(Date(Floor(Timestamp#(YourDateFieldName, 'YYYY-MM-DD hh:mm:ss')), 'DD/MM/YYYY')) as Date
From YourCalenderTable; //Resident YourCalenderTable;
//For time
//Time(Frac(Timestamp#(YourDateFieldName, 'YYYY-MM-DD hh:mm:ss')), 'hh:mm:ss TT') as Time
Kind regards,
Ishfaque Ahmed