8 Replies Latest reply: Oct 10, 2011 1:24 PM by yaman1510 RSS

    Using WHILE Statement, when loading data from Excel?

      Hello Guys,

       

      I am using the following code to load the data from an excel file, it's not showing any error but still no data is loaded after Reload.

       

      LOAD

            Field1, Field2,Field3,

            num#(Date(date1 + iterno ()-1,'YYYYMMDD')) as DATE_KEY,

           date(date1) as date1,

           date2,

           FROM Excel file

      while DATE1 + iterno () -1 <= DATE2;

       

      Cant we use WHILE here?

       

      Thanks in advance.

        • Re: Using WHILE Statement, when loading data from Excel?
          Miguel Angel Baeyens de Arce

          Hi,

           

          You need to change the syntax a bit, and use a DO LOOP loop, similar to what you are doing, and saving prior to the load the minimum value of date. Make sure as well that you are using all fields and variables case sensitive, otherwise it might return no errors but the table will not be loaded.

           

          What I'd do is something like

           

          MinMaxDate:
          LOAD Min(Date1) AS MinDate,
               Max(Date2) AS MaxDate
          FROM File.xls;
          
          // This way you always have the minimum and maximum static date possible stored in a variable
          LET vMinDate = FieldValue('MinDate', 1);
          LET vMaxDate = FieldValue('MaxDate', 1);
          
          DROP TABLE MinMaxDate;
          
          DO
               Table:
               LOAD Field1,
               Field2,
               Field3,
               Date(Date1) AS Date1,
               Date(Date2) AS Date2
               FROM File.xls
               WHERE Num(Date1) = $(vMinDate) + IterNo();
          LOOP WHILE  $(vMinDate) + IterNo() <= $(vMaxDate);
          

           

          Hope that makes sense.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • Re: Using WHILE Statement, when loading data from Excel?

              Hi Miguel,

               

              Thanks for replying to the post. I've tried using the above code, but it's not working properly. Actually, what I'm trying to achieve is that I've multiple start dates and end dates of certain tasks and that is my Date 1 and Date2. So, all I'm doing is using a loop to get duplicate data between the dates which are not there. That way I'm trying to generate data to connect to a calender so that I can get data for each day. Example: if value of a field is 'A' between certain date 1 and date 2, I've only these dates in my database(because my database is keeping a track only of the changes). So, the purpose is to generate duplicate rows that can help a user to select any date between these dates, and they can get the value of this field as 'A'

               

              I hope I'm clear in my explaination.

              Thanks and look forward to your reply.

                • Re: Using WHILE Statement, when loading data from Excel?
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  Although the code above will work with a few tweaking, it may not be the table I'd build based on what you say in your reply. I'd rather create a master calendar as in this post (among many many others you can find in the Community) and this application, and use the IntervalMacth() load as in this thread so any user can get the right results with no need to create additional records that can affect your aggregation functions and slower your load time and chart rendering.

                   

                  Hope that helps.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica

                    • Re: Using WHILE Statement, when loading data from Excel?

                      Hi Miguel,

                       

                      Thanks for posting this solution. I have a database which records certain tasks and have 2 date fields as created date and last modified date. The last_modified_date has a null value if there is no changes to the record till today. So, all I want is to display that if a user selects any date between the created date and the last modified date he should get the value of the corresponding fields. Thus, for what I've understood, I'm writing the code below and woould need your suggestions for the same:

                       

                      MyData:

                      LOAD

                      Date(Ceil(Rand() * 1460) + Date(min(LAST_MODIFIED_DATE))) AS CalendarDate, A,B,C;

                       

                       

                      SQL SELECT

                                "CREATED_DATE",

                           NVL(LAST_MODIFIED_DATE,sysdate) AS LAST_MODIFIED_DATE,

                          A,

                         B,

                         C                                        //A,B,C is some fields in the database.

                          FROM ABC.Table;

                         

                      CalendarTemp:

                      LOAD

                                Max(CalendarDate) AS DateMax,

                                Min(CalendarDate) AS DateMin

                      RESIDENT Sales;

                       

                       

                      LET vMaxDate = FieldValue('DateMax', 1);

                      LET vMinDate = FieldValue('DateMin', 1) -1;

                       

                       

                      DROP TABLE CalendarTemp;

                       

                       

                      MasterCalendar:

                      LOAD

                                Date(IterNo() + Date($(vMinDate))) AS CalendarDate,

                                Week(Date(IterNo() + Date($(vMinDate)))) AS CalendarWeek,

                                Month(Date(IterNo() + Date($(vMinDate)))) AS CalendarMonth,

                                Year(Date(IterNo() + Date($(vMinDate)))) AS CalendarYear,

                                InYearToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), 0) AS YTDFlag,

                                InYearToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), -1) AS LYFlag

                      AUTOGENERATE 1 WHILE Date(IterNo() + Date($(vMinDate))) <= Date($(vMaxDate));

                       

                      IntervalMatch:
                      INTERVALMATCH (CalendarDate) LOAD CREATED_DATE,
                         LAST_MODIFIED_DATE
                      RESIDENT MyData;

                      Is this seems correct to you?

                       

                      Thank you again,would really appreciate your reply on this.