2 Replies Latest reply: Jul 31, 2018 10:03 AM by Mohammed Al Radi RSS

    Master Calendar two date fields

    Mohammed Al Radi

      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;

        • Re: Master Calendar two date fields
          Ali Ahmad

          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

          [C:\Users\AliAhmad\Desktop\Test.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",

              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

            • Re: Master Calendar two date fields
              Mohammed Al Radi

              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