Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

8 Replies

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

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

Not applicable

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?

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

Not applicable

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.

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

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

Not applicable

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

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.

Not applicable

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

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

Not applicable

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

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.