Show data based on selected date (link date and datetime)

[Anonymous]

I have a list box of dates and a table which has datetime as dimension. The dates from list box is a separate table from that with complete date. I tried connecting them by cutting the datetime into just date (another field) in my select script but did not work. So I thought this should be done on the conditional of datetime dimension but I'm not sure how to do this. How should I be able to show data based on selected dates in Filters?

[felipedl · Accepted solution]

Hi Pamela,

From what your saying I believe what's happening is that the date on the right table is formated with a timestamp date, MM/DD/YYYY hh:mm:ss and you have on the left side only MM/DD/YYYY.

This might cause the date to not connect due to the following:

The date format is a numeric value like 42000, and when you have a timestamp, it goes something like 42000.10 (gets a decimal value to determine hour minutes and seconds).

If your date has only the integer part, it will not connect to the table on  the right.

You need to create a date on both side with:

Load

    *, // Whatever fields you have

    Date(floor(Date),'MM/DD/YYYY' as Date

From [whatever];

This quick example:

x:

load * Inline

[

Date

42000.10

42001.10

];

Y:

load * Inline

[

Date,A

42000,1

42001,2

];

Gives the following table (not associated data);

And after using floor function like the code bellow it gives:

x:

load * Inline

[

Date

42000.10

42001.10

];

Y:

load * Inline

[

Date,A

42000,1

42001,2

];

NoConcatenate

data:

Load

floor(Date)

Resident x;

drop table x;