Dear Qlik Community,
I hope this finds you well. I am currently working with a data-set that includes two date fields. One date field is a normal field (date loan received..column M in excel), and the other a datefield created via crosstable function (loan repayment instalments...column O onwards). Results in te app pertaining to the 'normal' date field seem to be correct but I cannot seem to replicate that for the crosstabled date field.
Please find enclosed the script I am working with (note I also tried a 'date-link table' as well and many different date functions). All dates in the data source (attached) are in DD/MM/YYYY format.
The data details a micro-loan programme (the cross-tabled section the monthly payments made by loan recipients). When I create a table in qlik to verify the data the 'date receipt' part functions and corresponds to the actual amounts and 'LoanMonthYear' field, but the 'installment payment' does not correspond to the LoanMonthYear field.
Prior to creating the mastercalender I tested both original date fields (bolded in the 'loans1' table and found the respective num & num# additions transformed each field into numeric dates.
If anyone has any ideas regarding a suitable data model/ date functions for this type of data set I would be most grateful. Thank you for your support!
Best,
Mohammed
[Loans]:
CROSSTABLE ([Installmentdate],[Installmentpayment],15)
LOAD
F1 AS Status,
RowNo() as row,
القيمة,
"Payment Amount per installment",
"Number of Payment installments Required",
"File Number",
"Family Size" as [Family Size Loans],
Gender as Genderloan,
"Maritual Status" as [Maritual Status Loans],
"SHC (Special Hardship)" as [SHC (Special Hardship) Loans],
"Pre Loan Monthly Income",
"Current Monthly Income",
"Date of Receipt",
"Loan Type",
[Loan Status],
"42019",
"42050",
"42078",
"42109",
"42139",
"42170",
"42200",
"42231",
"42262",
"42292",
"42323",
"42353",
"42384",
"42415",
"42444",
"42475",
"42505",
"42536",
"42566",
"42597",
"42628",
"42658",
"42689",
"42719",
"42750",
"42781",
"42809",
"42840",
"42870",
"42891",
"42931",
"42948",
"42979",
"43023",
"43054",
"43084",
"43115",
"43146",
"43174"
FROM [lib://wpa/xxxxsheet.xlsx]
(ooxml, embedded labels, table is Loans);
NoConcatenate
[Loans1]:
Load
Status,
row,
القيمة,
"Payment Amount per installment",
"Number of Payment installments Required",
"File Number",
Genderloan,
[SHC (Special Hardship) Loans],
[Family Size Loans],
[Maritual Status Loans],
[Loan Status],
"Pre Loan Monthly Income",
"Current Monthly Income",
"Loan Type",
num([Date of Receipt]) as LoanDate,
'Receipt' as LoanDateType,
[Installmentpayment]
Resident [Loans];
Load
Status,
row,
القيمة,
"Payment Amount per installment",
"Number of Payment installments Required",
"File Number",
Genderloan,
[SHC (Special Hardship) Loans],
[Family Size Loans],
[Maritual Status Loans],
[Loan Status],
"Pre Loan Monthly Income",
"Current Monthly Income",
"Loan Type",
num#([Installmentdate]) as LoanDate,
'installments' as LoanDateType,
[Installmentpayment]
Resident [Loans];
Drop table [Loans];
Temp:
//Load num#(LoanDate,'#') as NumericDate
// Load date#(date) as NumericDate
//Load date(LoanDate) as NumericDate
//Load Num#(LoanDate, '#') as NumericDate
Load Date (LoanDate) as NumericDate
RESIDENT Loans1;
MinMax:
LOAD
Min(NumericDate) AS MinDate,
Max(NumericDate) AS MaxDate
RESIDENT Temp;
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar:
LOAD Distinct
//Date(text(num(MonthStart(Date($(vMinDate) + RecNo() - 1))))) AS [LoanDate],
Date($(vMinDate) + RecNo() - 1) AS LoanDate,
Year(Date($(vMinDate) + RecNo() - 1)) as [LoanYear],
Month(Date($(vMinDate) + RecNo() - 1)) as [LoanMonth],
Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MM-YYYY') AS [LoanMonthYear],
'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As LoanQuarterYear
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);
DROP TABLE Temp;
DROP TABLE MinMax;
I made the date changes you kindly provided above and the date formats became unified but the master calendar still didn't link to the measures. What made it link in the end was included '[Full Name Loans]' in the 'Temp' table of the master calendar. I am assuming that it solidified the relation between the tables (I am not sure why exactly usually I don't put another field in the 'Temp' table). 
