Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

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
marcus_sommer

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
Champion
Champion
Author

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

marcus_sommer

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