Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
Could you attach the app here?
Use Floor(Date) as %CalDate.
It will remove milliseconds from your timestamp.
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.
Hi,
"Use advanced editor " to attach the app
Thank you for the suggestion but for some reason, I do not have that option any more. Is there a seting change required?
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
Peter, many thanks - this worked perfectly!
Hi Jerry.
I tweaked your suggestion a bit as well and I managed to also get this working.
Many thanks to you.