4 Replies Latest reply: Nov 30, 2014 2:04 PM by Peter Rieper RSS

    Flexibilize the LOAD of a bunch of Excel_files

    Friedrich Hofmann

      Hi,

       

      I have the requirement, in the context of a larger piece of code, to load the personnel_plans of our forklift_personnel.

      They have one plan per week. They copy it from one week to the next and just adapt the contents - and they still manage to build slight variations into the filename - one blank more or less, which is too much for QlikView - unless ...

      The general format is this:

      >>> Schichtplan_NTB1_Ivan KW33 Früh <<<

      <=> Sometimes, they make it like this (for instance)

      >>> Schichtplan_NTB 1_Ivan KW33 Früh <<<

       

      I am now trying to flexibilize that and I have a rather complex formula for that:

      >> MID(Dateiname, INDEX(REPLACE(UPPER(Dateiname), ' ', '_'), 'N', 2), 4)  <<

      => That is, I cut out 4 digits starting from the 2nd iteration of the letter 'N', which should then be the plant_name.

      The next thing I have to do is find out if within those 4 letters, there is an '_';

      <=> That's where I am currently stuck. It just won't work.

       

      Can you help me there?

      Thanks a lot!

      Best regards,

       

      DataNibbler

       

      P.S.: It seems there is some confusion of the '_' with the BLANK - QlikView doesn't seem to notice the '_' in those 4 letters, it doesn't show, so I cannot query if it is there ... it does notice a BLANK, though... How can that be, I have replaced BLANKs in the first step!?