Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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,
May be you have different timestamp in it
Try listbox with TimeStamp(Date).
If you to overcome this then use Date(Floor(Date))
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
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...
Hi Rodo,
I guess this is because of wrong format.
try like this:
date(floor(YourDateField),'YYYY/MM/DD') as NewDate;
Regards
KC
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))
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
Thank you Marco
This was my solution DayName(Date(Num#(Date))) as Date