6 Replies Latest reply: Mar 22, 2018 12:54 PM by Elizabeth Viso RSS

    Converting a list to Dates in the Script

    Elizabeth Viso

      Hello!

       

      I have data I am pulling from excel that has columns numbers 1 -31 which represent dates, so 1 is 3/1/2018 and so on. When load the script is there any way to change it do it's read as dates? I'd like to create a filter on an app of dates and they it is now I can't.

       

      My Excel sheet:

      excel.PNG

       

      And my Script:

       

      load.PNG

       

      Thank you!

        • Re: Converting a list to Dates in the Script
          Digvijay Singh

          Use Cross table load and later in resident load convert the new date field( which has values as 1,2,3,4,5) into the actual date like -

          MakeDate(2018,03,Date) as Datefield

            • Re: Converting a list to Dates in the Script
              Elizabeth Viso

              Got it! But I think I'm doing something wrong on my crosstable (first time I've tried this )

               

              TEMP:

              LOAD "_ROWNUM_" AS [Record ID],

                  Facility,

                  Room,

                  Humidity,

                  "Year",

                  "Month",

                  "1",

                  "2",

                  "3",

                  "4",

                  "5",

                  "6",

                  "7",

                  "8",

                  "9",

                  "10",

                  "11",

                  "12",

                  "13",

                  "14",

                  "15",

                  "16",

                  "17",

                  "18",

                  "19",

                  "20",

                  "21",

                  "22",

                  "23",

                  "24",

                  "25",

                  "26",

                  "27",

                  "28",

                  "29",

                  "30",

                  "31",

                  "# Compliant" AS [Number Compliant],

                  "% Compliant" AS [Percent Compliant];

               

              TEMP2:

              CrossTable([Date], DateField, 31)

              LOAD

              [Record ID],

                  Facility,

                  Room,

                  Humidity,

                  "Year",

                  "Month",

                  "1",

                  "2",

                  "3",

                  "4",

                  "5",

                  "6",

                  "7",

                  "8",

                  "9",

                  "10",

                  "11",

                  "12",

                  "13",

                  "14",

                  "15",

                  "16",

                  "17",

                  "18",

                  "19",

                  "20",

                  "21",

                  "22",

                  "23",

                  "24",

                  "25",

                  "26",

                  "27",

                  "28",

                  "29",

                  "30",

                  "31",

                 "Number Compliant",

                 "Percent Compliant"

              RESIDENT TEMP;

               

              Drop Table TEMP; 

               

               

              [ALL DATA]:

              LOAD [Record ID] AS ID,

              MakeDate(2018,03,Date) AS DateField,

                  *

              Resident TEMP2;

               

               

              Drop Table TEMP2;

               

               

              Drop Fields [Date];