topic left join and dates in QlikView

left join and dates

rido1421 — Fri, 07 Nov 2014 04:56:45 GMT

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?

Re: left join and dates

sujeetsingh — Fri, 07 Nov 2014 04:58:44 GMT

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

Re: left join and dates

rido1421 — Fri, 07 Nov 2014 05:05:59 GMT

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,

Re: left join and dates

CELAMBARASAN — Fri, 07 Nov 2014 05:09:07 GMT

May be you have different timestamp in it

Try listbox with TimeStamp(Date).

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

Re: left join and dates

jonathandienst — Fri, 07 Nov 2014 05:18:52 GMT

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

Re: left join and dates

rido1421 — Fri, 07 Nov 2014 05:19:31 GMT

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

Re: left join and dates

jyothish8807 — Fri, 07 Nov 2014 05:21:02 GMT

Hi Rodo,

I guess this is because of wrong format.

try like this:

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

Regards

Re: left join and dates

CELAMBARASAN — Fri, 07 Nov 2014 05:23:38 GMT

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

Re: Re: left join and dates

MarcoWedel — Sat, 08 Nov 2014 15:50:04 GMT

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

Re: left join and dates

rido1421 — Tue, 11 Nov 2014 06:53:30 GMT

Thank you Marco

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

Re: left join and dates

MarcoWedel — Tue, 11 Nov 2014 07:14:24 GMT

You're welcome.

If this expression works for you, then maybe

DayName(Num#(Date)) as Date

will also return correct results.

Hope this helps

Regards

Marco