Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

malradi88
Contributor

Master Calendar two date fields

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;

1 Solution

Accepted Solutions
malradi88
Contributor

Re: Master Calendar two date fields

Hi Ali,

Thank you for this 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).

Best,

Mohammed

2 Replies
bwisealiahmad
Valued Contributor

Re: Master Calendar two date fields

Hi,

So I changed the script a bit and did the num# and Date part in the tables while concatenating so ensure both of them got into date format.

Is this correct?

Script:

[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

(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",

    Date(Num#([Date of Receipt]),'DD.MM.YYYY') 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",

    Date(Num#([Installmentdate]),'DD.MM.YYYY') as LoanDate,

      'installments' as LoanDateType,

    [Installmentpayment]

Resident [Loans];

Drop table [Loans];

Temp:

Load

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($(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;

See app attachment.

Best,

Ali A

malradi88
Contributor

Re: Master Calendar two date fields

Hi Ali,

Thank you for this 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).

Best,

Mohammed

Community Browser