Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

datanibbler
Esteemed Contributor

FileList with BLANKs in variable places?

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: FileList with BLANKs in variable places?

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

6 Replies
MVP & Luminary
MVP & Luminary

Re: FileList with BLANKs in variable places?

Hi DataNibbler,

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

- Marcus

datanibbler
Esteemed Contributor

Re: FileList with BLANKs in variable places?

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?

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

Let me know

datanibbler
Esteemed Contributor

Re: FileList with BLANKs in variable places?

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

MVP & Luminary
MVP & Luminary

Re: FileList with BLANKs in variable places?

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

datanibbler
Esteemed Contributor

Re: FileList with BLANKs in variable places?

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!