Data not linking

[#1]

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.

[Anonymous]

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.

[sathishkumar_go]

Could you attach the app here?

[jerrysvensson]

Use Floor(Date) as %CalDate.

It will remove milliseconds from your timestamp.

[#5]

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.

[sundarakumar]

Hi,

"Use advanced editor " to attach the app

[#7]

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

[Peter_Cammaert · Accepted solution]

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

[#9]

Peter, many thanks - this worked perfectly!

[#10]

Hi Jerry.

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

Many thanks to you.