Case 528 How to link my sales order and GL table data to time line ?

[paulyeo11]

Hi All

I have 2 Table :-
GL Table - Date field name = date_GL
SALES ORDER Table - Date field name = date_SO

My below script is how i link my sales order and GL Table data field :-

tmp:
LOAD
min(date_GL) AS MinDate,
max(date_GL) AS MaxDate
RESIDENT GL_TABLE;
CONCATENATE (tmp)
LOAD
min(date_SO) AS MinDate,
max(date_SO) AS MaxDate
RESIDENT SALES_TABLE;
MaxMinDate:
NOCONCATENATE LOAD
MIN(MinDate) AS MinDate,
MAX(MaxDate) AS MaxDate
RESIDENT tmp;
DROP TABLE tmp;
LET varMinDate = Num(Peek('MinDate'));
LET varMaxDate = Num(Peek('MaxDate'));
LET vToday = num(today());
DROP TABLE MaxMinDate;
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS Num,
date($(varMinDate) + rowno() - 1) AS TempDate,
date($(varMinDate) + rowno() - 1) AS D,
year($(varMinDate) + rowno() - 1) AS Y,
month($(varMinDate) + rowno() - 1) AS M,
date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

May i know it is correct ? As My master claendar table is not link with sales order and GL data.

Paul Yeo

[Saravanan_Desingh · Accepted solution]

Here the SO and GL are already connected with SOURCE_. So, if you add one more key, then it will end up in Sync Key.

The solution for such situation is Link Table. Please check the below link for simple example on Link Table.

https://www.learnallbi.com/link-table-in-qlikview/

[paulyeo11]

Hi Sir

How about link using canonical date approach? Can give me some ideas. I have read the blog post but unable to understand how to apply.

Paul

[Saravanan_Desingh]

Hi @paulyeo11 . Sorry I have not used it before. May be you can try and let me know the outcome. I may need  sometime to understand the concept of it.

[paulyeo11]

Hello ,
Thank you very much for your sharing , it work now.

Can you help me on below link :-

https://community.qlik.com/t5/New-to-QlikView/Case-836-How-to-link-my-SOURCE-filed-with-SOURCE/td-p/...

Paul