joining date qvd

[#1]

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

[Anonymous]

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]

[sunny_talwar]

Try this:

Load...

...

date(Floor(DATE)) as [Calendar date]

left join (table name)

load

[Calendar Date]

....

from....

[Anonymous]

You can also try date(DayStart(Date))

[engishfaque]

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