topic Re: FileList with BLANKs in variable places? in QlikView

FileList with BLANKs in variable places?

datanibbler — Fri, 30 Jan 2015 12:37:39 GMT

Hi,

I have the requirement of setting up a loop to LOAD a number of Excel files.

The contents of the Excel files is always equal in structure, so that is not the problem

<=> The filename is written with slight differences in all sorts of places.

The general format is like this:

>> Schichtplan_NTB 1_Sergej KW 05Früh <<

I have to grab one part of this to use for my file_mask (for a FOR EACH loop);

- "Schichtplan" is not sufficient for there are some from 2015 which are completely different.

- "NTB1" is the name of the plant, so that would be good - but as you see, there is a BLANK here - but not always 😉

<=> I could use "Schichtplan_NTB*" - but if/when we do the same in the other plant (that is NTB2), I will face the challenge of keeping the naming_format uniform and still distinguishable.

- Before I actually open the loop, of course i do not know exactly how the file_names are written and I cannot grab them and purge the BLANKs beforehand.

=> Is there any elegant way around this (except educating the users which seems to be - hm, difficult ...)

Thanks a lot!

Best regards,

DataNibbler

Re: FileList with BLANKs in variable places?

marcus_sommer — Fri, 30 Jan 2015 12:46:10 GMT

Hi DataNibbler,

couldn't you use a loop through a dirlist/filelist? Then the filename won't be important.

- Marcus

Re: FileList with BLANKs in variable places?

datanibbler — Fri, 30 Jan 2015 12:51:43 GMT

Hi Marcus,

what do you mean?

I am using a filelist right now - the problem is, in the archiving_directory for 2014 about half the files are completely different and unusable for me - there has been a change in layout around summer and I can use only the files from that time on.

I am using a FOR EACH loop w ith a filelist to grab the '"meta_infos" about the files (plant, shift, week) - then I have another loop, this time a FOR loop, where I get the actual file_contents and join those to the meta_infos.

Re: FileList with BLANKs in variable places?

alexandros17 — Fri, 30 Jan 2015 12:51:46 GMT

To delete blanks use Replace(myText, ' ', '') so all your strings will have blanks deleted

Let me know

Re: FileList with BLANKs in variable places?

datanibbler — Fri, 30 Jan 2015 12:57:06 GMT

Hi Alessandro,

I know that function. The trick is, to use this, I need the filename first so I can apply the function.

The exact point in the script where I have a challenge is where I use the SET command to define a filelist for my FOR EACH loop (I also use the DIRECTORY command, so my filelist actually contains only a mask for the file_name).

At that point, I don't actually have the file_names, so I cannot apply either that or the Purgechar() function.

Best regards,

DataNibbler

Re: FileList with BLANKs in variable places?

marcus_sommer — Fri, 30 Jan 2015 12:57:34 GMT

Hi DataNibbler,

maybe you could check the filetime() or filesize() within the loop. If this isn't enough you could read the first row to check the data-structure.

- Marcus

Re: FileList with BLANKs in variable places?

datanibbler — Fri, 30 Jan 2015 13:15:00 GMT

Hi Marcus,

that seems to be the best solution.

All the files for 2015 (so far) are usable for me - I only have to purge BLANKs, which I can always do once I have actually opened the loop.

My dilemma was just before I do so, and only for the files from 2014. By checking the filetime, I have another way of filtering for the time (starting from the end of March 2014).

That seemingly solves my dilemma.

Thanks a lot!