topic Re: joining date qvd in QlikView

joining date qvd

Wed, 25 Nov 2020 16:16:04 GMT

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.

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

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

Re: joining date qvd

Anonymous — Thu, 03 Mar 2016 18:32:53 GMT

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]

Re: joining date qvd

sunny_talwar — Thu, 03 Mar 2016 18:33:06 GMT

Try this:

Load...

...

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

left join (table name)

load

[Calendar Date]

....

from....

Re: joining date qvd

Anonymous — Thu, 03 Mar 2016 23:25:59 GMT

You can also try date(DayStart(Date))

Re: joining date qvd

engishfaque — Fri, 04 Mar 2016 04:34:09 GMT

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