topic Re: Data not linking in QlikView

Data not linking

Fri, 29 Nov 2013 11:33:10 GMT

Hi everyone.

I have two tables each to be linked by the fields which I named '%CalDate'. One loads dates (amongst other fields) from an excel which I first re-formatted in my script into the correct date format (to match the other table's date format) and stored first as a QVD. I then loaded off that QVD and ensured again that the format of the dates in my date field (%CalDate) remained the same. Despite the field names and data looking identical in both tables, QlikView is not linking the data. In other words, if I click on say '201304' in the one table, I get no data from the other table, even though the same dates exist! On the table structure view, you will see that the tables do however link on the %CalDate fields.

Do you perhaps have any suggestions to help me with please?

Thank you.

Re: Data not linking

Anonymous — Fri, 29 Nov 2013 12:07:37 GMT

Only for the linking date field convert the date to a number in both the dataset.

use

Fabs(Date) as %CallDate,

This will ensure that there are no date format issues in the link and the dashboard performance will also be good as the link is numeric.

Re: Data not linking

sathishkumar_go — Fri, 29 Nov 2013 12:21:20 GMT

Could you attach the app here?

Re: Data not linking

jerrysvensson — Fri, 29 Nov 2013 12:44:52 GMT

Use Floor(Date) as %CalDate.

It will remove milliseconds from your timestamp.

Re: Data not linking

Mon, 02 Dec 2013 06:20:14 GMT

Hi everyone.

Apologies for taking so long to reply - just experiencing a bit of issues with the QlikView site since last week.

@Aadil, @Jerry: Thank you for your suggestions. I tried both but still no luck though.

@Satish: For some reason I am not getting full functionality of QlikView at the moment - having some issues with the site. I cannot seem to attach the file. It only gives me the option to attach a video or image. Could you please point me in the right direction?

Thank you guys.

Re: Data not linking

sundarakumar — Mon, 02 Dec 2013 06:27:19 GMT

Hi,

"Use advanced editor " to attach the app

Re: Data not linking

Mon, 02 Dec 2013 06:32:31 GMT

Thank you for the suggestion but for some reason, I do not have that option any more. Is there a seting change required?

Re: Data not linking

Peter_Cammaert — Mon, 02 Dec 2013 08:55:28 GMT

As others have hinted to, there is a problem with the content of your link field.

It seems that you are using sort of Period values (YYYYMM) instead of real dates in %CalDate. What you can do to solve this, is to use MonthStart() of the raw date values of both %CalDate columns to force them into an identical range, e.g. always 1/MM/YYYY 00:00:00.000 Then apply Date() to attach a YYYYMM format to the raw date values. In short:

LOAD ... Date(MonthStart(RawExcelDateColumn), 'YYYYMM') AS %CalDate, ...

Best,

Peter

Re: Data not linking

Mon, 02 Dec 2013 12:20:27 GMT

Peter, many thanks - this worked perfectly!

Re: Data not linking

Mon, 02 Dec 2013 12:47:01 GMT

Hi Jerry.

I tweaked your suggestion a bit as well and I managed to also get this working.

Many thanks to you.