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.
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.
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.
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:
Date(Ceil(Rand() * 1460) + Date(min(LAST_MODIFIED_DATE))) AS CalendarDate, A,B,C;
NVL(LAST_MODIFIED_DATE,sysdate) AS LAST_MODIFIED_DATE,
C //A,B,C is some fields in the database.
Max(CalendarDate) AS DateMax,
Min(CalendarDate) AS DateMin
LET vMaxDate = FieldValue('DateMax', 1);
LET vMinDate = FieldValue('DateMin', 1) -1;
DROP TABLE CalendarTemp;
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 (CalendarDate) LOAD CREATED_DATE,
Is this seems correct to you?
Thank you again,would really appreciate your reply on this.
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?
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