what is wrong with my Link table created for common date column ?
what is wrong in the below link table, my counts are increased when i use the derived yearmonth column as a dimension compared to disc_deactivation_date.
MinMaxDates: LOAD Min(Common_Date) as MinDate, Max(Common_Date) as MaxDate RESIDENT LinkTable;
LET vMinDate = Peek('MinDate', 0, 'MinMaxDates'); LET vMaxDate = Peek('MaxDate', 0, 'MinMaxDates');
DROP TABLE MinMaxDates;
NoConcatenate
Calendar: LOAD Date($(vMinDate) + IterNo() - 1) as Common_Date AUTOGENERATE 1 WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
MasterCalendar: LOAD Common_Date, Year(Common_Date) as Year, Month(Common_Date) as Month, 'Q' & Ceil(Month(Common_Date)/3) as Quarter, year(Common_Date)&''&Num(Month(Common_Date)) as YearMonth RESIDENT Calendar; // Load from the previously generated table
Drop Tables LinkTable, Calendar;
my final data model is like common_date in master calendar connected to Link_Table and from Link_table subscr_id connected with the Latest_Base table.
From the technical standpoint, you are missing the DISTINCT keyword in your Link Table loads. Your dates are loaded multiple times and that is likely causing duplication of data.
From the business standpoint, I'm not sure this is the kind of linking that should be done here. It looks like you are loading different dates into the same field "Common Date". I understand your desire to have a common calendar, but as a side effect, you might be associating different data tables by the two date fields that might have different meanings in these two tables (unless these multiple fields are indeed the same in essence).
Also, this link table only makes sense if you have other dimensions involved in the linking. Just for the purpose of combining all date fields into one, you could simple rename these fields, or load a copy of these fields with the name "Common Date" directly in their respective tables. The results will be the same, without the extra Link Table.
Allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, covering some of these challenges, along with advanced scripting, performance optimization, and advanced aggregation and Set Analysis techniques. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.
In short my requirement is to mimic the below teradata code into Qlik. where I have mutliple data columns in the single final Base table and want to have a single common_date column. please suggest where i am going wrong.
SELECT B.Subscr_Id, tld.disc_start_date, tld.disc_end_date, Life.Start_Date,Device_Start_Date, Device_End_Date Life.End_date,FROM Base B LEFT JOIN discount_hist tld ON B.subscr_id = tld.subscr_id AND B.calendar_date BETWEEN tld.disc_start_date AND tld.disc_end_date LEFT JOIN Subscr_Life_Cylce Life ON B.subscr_id = Life.subscr_id AND B.calendar_date BETWEEN Life.Start_Date AND Life.End_date
LEFT JOIN Device D ON B.subscr_id = D.subscr_id AND B.calendar_date BETWEEN Device_Start_Date AND Device_End_date.
Looking at the SQL code, I think you need to explore the IntervalMatch solution - that one will allow you to associate single dates with intervals of start and end dates.
For the purpose of attaching calendars to several different dates you could either used derived calendars, created by Qlik Sense automatically, or search for "Canonical Date" solutions - there are several blog articles about that.
You drop the wrong table with the distinct loaded LinkTable instead of the previous Link_Table - whereby the second resident-load is superfluous because the distinct could be added within the concatenate-steps.
Beside this it's not quite clear which date-information are really required. If the start/end information are only necessary to filter the data-set to a certain sub-set the sql where-conditions are suitable. But if a resolution of the start/end to months, years and similar period-information are wanted then take a look for the suggested IntervalMatch - Qlik Community - 1464547.
Like already hinted may the link-table not be the most suitable data-model especially if all dates come from a single table and containing the same information (like sales/forecast/budget which differ only in the direction of the view) or are parts of an event-chain (like sales --> billing --> shipment). Personally I would keep them within the fact-table by splitting the parts - concatenating them together - similar to your date-outsourcing in the linktable but containing all other relevant data (ID's, KPI's) by harmonizing the field-names and data-structures.
