Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

New instance of a well-known problem... LOOP running empty


Hi,

I once again face an issue concerning the failure of a LOAD statement that encounters no records.

The challenge is this:

- I am displaying data from a database that just started operating in August.

=> Thus, there is no data from Jan to July.

- I would like to build my Dashboard parallel to one I already have, which displays the same kind of data, but from a database that
  has been operative for some years.

- In the old Dashboard, I have a LOOP in the script:

     - It walks through the values 1 through 12 (for the months)

     - For every month, the LOAD statement sums up the amounts.

     => That enables me, on the GUI, to use a set_expression to define the month with the highest total within this year and display
           that.

- In the new Dashboard, I would like to do the same (getting Dashboards to look uniform is very important here)

<=> The LOOP cannot be the same, at least not yet, for there is no data for the first 7 months -> that in itself would not be a problem,
        I would just load the month and a 0 in that case, I only want to make sure the LOOP continues.

Now, in principle, I have done this in another scenario - finding out whether a LOAD was successful and, in case it wasn't, loading something constructed - but that was not in a LOOP.

Can I create - and, more importantly, change - variables inside a LOOP?

Otherwise, I would just have to create 12 variables in the script - for 12 months - and then query on of them in every iteration of the LOOP

Can anybody help me in this?

Thanks a lot!

Best regards,

DataNibbler

3 Replies

Re: New instance of a well-known problem... LOOP running empty

I think my issue was similar to yours and I have it so solved:

let vCounter = ((year(today() - 1) - year('01.02.2012')) * 12) + (month(today() - 1) - month('01.02.2012'));

for i = 0 to $(vCounter)

    let vZeitraum = date(AddMonths('01.02.2012', $(i)), 'YYYY_MM');

    LOAD *

    FROM [$(ftp)$(datafile)$(vZeitraum)$(ext)] (txt, codepage is 1252, embedded labels, delimiter is ';', no quotes);

next

- Marcus

datanibbler
Esteemed Contributor

Re: New instance of a well-known problem... LOOP running empty

Hi Marcus,

if I understood your code correctly, you knew at what date (or month) to begin your LOOP - the start_date was apparently Feb_2012.

Ah, I see - you count 12 for every year past since then and add the nr. of months past since then.

Apparently, in your case, there was a separate file to load for each month.

Thanks for sharing!

Well, my scenario is in one aspect a bit easier - yet - for I am loading from an Excel file that, so far, is for one year only. That, however, is supposed to change so that next year one will be able to look at this year's data in QlikView.

So I will also have to think of a way to make the code flexible so there will not be malfunctions all over the place on January 2, 2014... we have a load of Excel lists like this ;-)

Thanks a lot!

Best regards,

DataNibbler

Re: New instance of a well-known problem... LOOP running empty

Perhaps here are another ideas for loading not well known excel-files - to read the datastructure before and to react appropriate: Use excel sheet name in field

- Marcus

Community Browser