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

Clean-up routine to load in a FOR_EACH loop

Hi,

I have to build a FOR EACH loop using a searchmask for files created in a subroutine - there might be I don't know how many, one generated in every iteration.

The format is 'SOFA_n.qvd' - where n is a number.

There are other files named like 'SOFA_nnnn.qvd', so it is important to specify that there is just one number behind the underscore - or I'd have to correct several parts of the script.

I have seen in the help_file that the '?' is a wildcard for exactly one digit?

So far, I have

>> SET v_fileformat_SOFA_gesäubert = 'SOFA_?.qvd'; <<

Can anybody tell me what's wrong here? When I put an EXIT SCRIPT right behind that, the variable is not there.

Thanks a lot!

Best regards,

DataNibbler

14 Replies
alexandros17
Partner - Champion III
Partner - Champion III

Try using let instead of set

datanibbler
Champion
Champion
Author

Hi Alessandro,

SET is ok. I was doing something wrong, or maybe I just overlooked the variable, I have so many, that's a problem. I tested just that piece of code in a small app and it works fine.

The '?' is a placeholder for exactly one digit, so the searchmask won't fit qvd_files that are named like "SOFA_unsauber.qvd", correct?

That is an important point of course.

Thanks a lot!

alexandros17
Partner - Champion III
Partner - Champion III

Yes ? stands for just one char.

datanibbler
Champion
Champion
Author

Thanks!

I have a problem here now. There is just one file in this instance, "SOFA_1". So I want to load this in a loop.

To know the exact fieldnames - I want to avoid the * in a LOAD - I loaded the table as a standalone in a separate app.

There, the table loads quite normally and there is a field called "Werk_unsauber".

=> So I know the name of all the fields and I can write my LOAD into the loop, taking care to get a specific order of fields and to use the AS option every time - I want to append the LOAD to another one later, so the names have to be equal.

Still, I kept getting an error "Field not found >Werk_unsauber<

I copied it over from the standalone_LOAD several times to make sure there was no typo.

Then I just tried loading the file in the loop with an * and just look what the fields are like.

<=> now I get some xml stuff - describing the correct record, though.

I cannot make head or tail of this - the file is definitely qvd, not txt, my searchmask says *.qvd, and I have a counter_variable made up of part of the searchmask because one field has that as a suffix.

Can you help me there?

P.S.: I have used Filename() and confirmed that the code is definitely loading the correct file - and in my separate test_app, it's loading all normal ...

P.P.S.: Must be that * - when I do a LOAD * in the test_app, I get the same crap. When I type up every fieldname instead, it's fine...

It looks like that is a txt file - but how can that be?

Not applicable

If your file extension is QVD, you didn't find any problem.

Please use FileList function

marcus_sommer

Hi DataNibbler,

sometimes there are error-messages which are not quite true because they shows a consequential error and not the real reason. I have had already such "Field not found" errors and the reasons was errors with the file-path or tablename or it was missing the fileformat like a.qvd (qvd). Maybe you could use "set ErrorMode" and ScriptError to find out what goes wrong.

- Marcus

datanibbler
Champion
Champion
Author

Hi all,

I found the error. I omitted the (qvd) at the end of the LOAD statement and QlikView tried to load that qvd file as a txt. That's how that xml stuff came about.

I just kept repeating my own LOAD and the wizard and ended up trying different formats and hit the one looking exactly like what I was getting.

So that's solved. One more down, 600 to go ...

datanibbler
Champion
Champion
Author

Hi all,

there is something more:

There is one field in those tables I want to load in the loop - now I have an example_scenario where there are two. They both have the nr. of the iteration where they were created as a suffix, so they are named

- SOFA_1

- SOFA_2

I want to load them in a loop and I have to generate that suffix to get the fieldname right.

=> So I have it like

FOR each File in FileList()

  LET v_Ctr = MID($(File), 6, 1);

NEXT

(Ofcourse, there is the LOAD inside the loop, but that's not the problem. Seemingly QlikView correctly sets that counter to 1 for the file 'SOFA_1.qvd' and loads it all right - but in the second iteration, it does not set it to 2 (for 'SOFA_2.qvd'), but the counter remains at 1 and the LOAD consequently fails.

What might it be this time?

Thanks a lot!

datanibbler
Champion
Champion
Author

OK,

this too is solved. Instead of the variable, I can just use MID(Filename(), 6, 1) and I get the same result. That one works.