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

Using WHILE Statement, when loading data from Excel?

yaman1510 Sep 29, 2011 3:24 PM

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 Sep 29, 2011 4:49 PM (in response to yaman1510)
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
Like Show 0 Likes (0)

Actions
- Re: Using WHILE Statement, when loading data from Excel?
  
  yaman1510 Oct 3, 2011 1:22 PM (in response to Miguel Angel Baeyens de Arce )
  
  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.
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Using WHILE Statement, when loading data from Excel?
    
    Miguel Angel Baeyens de Arce Oct 3, 2011 1:43 PM (in response to yaman1510)
    
    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
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Using WHILE Statement, when loading data from Excel?
      
      yaman1510 Oct 4, 2011 3:19 AM (in response to Miguel Angel Baeyens de Arce )
      
      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.
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Using WHILE Statement, when loading data from Excel?
        
        Miguel Angel Baeyens de Arce Oct 4, 2011 6:00 AM (in response to yaman1510)
        
        Hi,
        
        I used the Rand() function to create data using AUTOGENERATE, but you are loading from an actual data source, so you don't need the Rand(). I don't see what you expect using Date(Min(LAST_MODIFIED_DATE)), probably I'm missing something. Can you post a sample app with actual data and what would you want to get?
        
        Regards.
        
        Miguel Angel Baeyens
        BI Consultant
        Comex Grupo Ibérica
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Using WHILE Statement, when loading data from Excel?
        
        yaman1510 Oct 4, 2011 1:15 PM (in response to Miguel Angel Baeyens de Arce )
        
        Hi Miguel,
        
        Here is the sample App. Earlier I was using a while statement durig Load and duplicating the data. You'll get a good idea of what I'm trying to achieve.
        
        Thanks a lot. I really appreciate your efforts.
        
        SampleApp.qvw 161.8 K
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Using WHILE Statement, when loading data from Excel?
        
        yaman1510 Oct 5, 2011 4:05 PM (in response to yaman1510)
        
        Hi Miguel,
        
        Does this sample app I posted above made any sense? I'm actually looking for some solutions to this urgently.
        Would really appreciate your reply on this.
        
        Thanks .!!
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Using WHILE Statement, when loading data from Excel?
        
        yaman1510 Oct 10, 2011 1:24 PM (in response to yaman1510)
        
        Hello Everyone,
        
        I'm still looking for a solution to this problem above. I would really appreciate if anybody who has experienced a similar problem can share a soltuion to this.
        
        Thanks.
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post