topic Re: Clean-up routine to load in a FOR_EACH loop in QlikView

Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 09:54:10 GMT

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

Re: Clean-up routine to load in a FOR_EACH loop

alexandros17 — Fri, 10 Oct 2014 09:57:45 GMT

Try using let instead of set

Re: Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 10:43:43 GMT

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!

Re: Clean-up routine to load in a FOR_EACH loop

alexandros17 — Fri, 10 Oct 2014 11:53:24 GMT

Yes ? stands for just one char.

Re: Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 13:02:46 GMT

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?

Re: Clean-up routine to load in a FOR_EACH loop

Fri, 10 Oct 2014 13:28:57 GMT

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

Please use FileList function

Re: Clean-up routine to load in a FOR_EACH loop

marcus_sommer — Fri, 10 Oct 2014 13:36:35 GMT

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

Re: Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 13:42:14 GMT

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

Re: Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 14:11:57 GMT

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);

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

Re: Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 14:20:12 GMT

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.

Re: Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 14:26:08 GMT

O no - it doesn't quite work.

The counting_variable is now properly generated - but the fieldname is not recognized.

I have the fields

- Sachnr_und_Anz_1

- Sachnr_und_Anz_2

My counter is 1 and 2, so in the LOAD I write

LOAD

Sachnr_und_Anz_$(v_Ctr) as newname

FROM

What's wrong with that? Can't I use that in a loop?

Re: Clean-up routine to load in a FOR_EACH loop

marcus_sommer — Fri, 10 Oct 2014 14:49:02 GMT

Loading with variables as field(parts) or fieldnames is generally difficult but it should work. Maybe you need a different syntax like:

Sachnr_und_Anz_'$(v_Ctr)' as newname

'Sachnr_und_Anz_$(v_Ctr)' as newname

Sachnr_und_Anz_v_Ctr as newname

or you create the complete field outside from loop in a variable:

let v = 'Sachnr_und_Anz_' & '$(v_Ctr)';

and then

$(v) as newname

- Marcus

Re: Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 14:52:21 GMT

Hi Marcus,

I'll try those variations.

I've already tried creating the entire fieldname as a variable, but how shall I do that outside the loop? I have a FOR EACH loop, the counter does not exist or is not incremented outside the loop.

P.S.: If all that doesn't work, I will just have to drop all superfluous fields so I have only those I need (to append that file to the one before) and rename the fields so I can just use a LOAD * lateron. (the files are stored and dropped at that point anyway, to be loade again only for the final assembly)

Re: Clean-up routine to load in a FOR_EACH loop

marcus_sommer — Fri, 10 Oct 2014 15:17:33 GMT

I meant inside the loop but outside the load - directly above the load-statement.

- Marcus

Re: Clean-up routine to load in a FOR_EACH loop

datanibbler — Fri, 10 Oct 2014 15:33:33 GMT

Hi Marcus,

no, sorry, all that didn't work.

So I settled for the longer way of renaming all the fields inside the subroutine, just before storing the table away and dropping it. So that in the final assembly, inside the loop, I can just use the * in the LOAD and all is fine.

Finally.

But that's only one list again - I'm curious to know if the same code will do the job on the other two now. Eventually I'll have it running over all three lists - and then I'm sure the people populating those lists will have a new idea ...