left join and dates

[rido1421]

Hi All

I have done a left join in my script im joining a table with Dates and Budgets to a table with

Budgets and a few other dimensions

after the join i have more than one date for each date... is it because of the left join?

how can I fix this?

[sujeetsingh]

Please explain the details of the tables,

on which field you have joined it it may be due to one to many relationship in two tables.

Please provide some inline table structure and data

[rido1421]

Ive noticed that even before the join I had this issue

When I pull dates as is i get a number, so ive converted it into a date as per below

but I get multiple dates... i think this is where my issue lies...

Dates:

Load

Date as Date1,

Date(Num#(Date)) as Date,

[CELAMBARASAN]

May be you have different timestamp in it

Try listbox with TimeStamp(Date).

If you to overcome this then use Date(Floor(Date))

[jonathandienst]

Hi

A date in Qlikview is a number, formatted to display as a date, just like Excel.The Date() function formats the number to look like a date, but does not change the underlying number. If your date has a time component, that is not removed when you use Date(), so you need to floor the dates to remove the time (the fractional component). So you need something like

     Date(Floor(Date)) As Date,

     Time(Frac(Date)) As Time,

HTH

Jonathan

[rido1421]

TimeStamp(Date) this works in the listbox

so Date(Timestamp(Date)) should fix this?

but when i do this in the script it doesnt give any result...

[jyothish8807]

Hi Rodo,

I guess this is because of wrong format.

try like this:

date(floor(YourDateField),'YYYY/MM/DD') as NewDate;

Regards

KC

[CELAMBARASAN]

If you want Date alone you need to use Floor, Actually QlikView keeps the Date as numbers Where 1=24 hrs of day, so Time will be in decimal(Kind of 41950.453472222). Floor will remove the decimals.

Date(Floor(Date))

[MarcoWedel · Accepted solution]

Hi,

it's indeed the difference between displaying the same date and the underlying values being fractional numbers with the same integer part.

Another (shorter) solution to get rid of the fractional part and formatting as a date is the DayName() function:

LOAD *,
    DayName(Date1) as Date,
    Money(Ceil(Rand()*1000)) as Budget1
Inline [
Date1
41913.000000
41913.0000001
41913.0000002
41944.000000
41944.0000001
41944.0000002
41974.000000
41974.0000001
41974.0000002
];

hope this helps

regards

Marco

[rido1421]

Thank you Marco

This was my solution DayName(Date(Num#(Date))) as Date