Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rido1421
Contributor II

left join and dates

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?

1 Solution

Accepted Solutions

Re: Re: left join and dates

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

];

QlikCommunity_Thread_141193_Pic1.JPG.jpg

QlikCommunity_Thread_141193_Pic2.JPG.jpg

hope this helps

regards

Marco

10 Replies
sujeetsingh
Honored Contributor III

Re: left join and dates

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
Contributor II

Re: left join and dates

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

May be you have different timestamp in it

Try listbox with TimeStamp(Date).

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

MVP
MVP

Re: left join and dates

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rido1421
Contributor II

Re: left join and dates

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
Honored Contributor II

Re: left join and dates

Hi Rodo,

I guess this is because of wrong format.

try like this:

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

Regards

KC

Best Regards,
KC

Re: left join and dates

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

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

];

QlikCommunity_Thread_141193_Pic1.JPG.jpg

QlikCommunity_Thread_141193_Pic2.JPG.jpg

hope this helps

regards

Marco

rido1421
Contributor II

Re: left join and dates

Thank you Marco

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

Community Browser