Using WHILE Statement, when loading data from Excel?

[#1]

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.

[Miguel_Angel_Baeyens]

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

[#3]

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.

[Miguel_Angel_Baeyens]

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

[#5]

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.

[Miguel_Angel_Baeyens]

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

[#7]

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.

[#8]

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 .!!

[#9]

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.