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

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?

marcus_sommer

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

datanibbler
Champion
Champion
Author

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)

marcus_sommer

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

- Marcus

datanibbler
Champion
Champion
Author

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