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

Flexibilize the LOAD of a bunch of Excel_files

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

4 Replies
marcus_sommer

Hi DataNibbler,

such string-operations are always ugly and it's nearly impossible all possible combinations to query. I think you need the same transformations for Dateiname within the mid-function to avoid confusion:

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

Maybe you could also use subfield(Dateiname, '_', 3) to simplify your expression.

- Marcus

Gysbert_Wassenaar

If the plant name is supposed to be the part between the two underscores try:

purgechar(subfield(Dateiname, '_',2), ' ')

or

purgechar(textbetween(Dateiname, '_','_'), ' ')


talk is cheap, supply exceeds demand
datanibbler
Champion
Champion
Author

Hi Marcus,

I agree, that would be much easier - but they sometimes introduce one more BLANK, so I cannot simply use Subfield() - I have to work with INDEX().

I think I have now made it - though I still don't know why, after I had already replaced the BLANKs, QlikView still didn't "see" an underscore, but a BLANK - I'll have another look.

prieper
Master II
Master II

Hi,

not quite clear, what you actually try to extract:

Looking at the filename it seem to be:

Schichtplan_NTB1_Ivan KW33 Früh

"Schichtplan_" + Plant + "_" + Name + Week(start with KW) + Shift

If your only problem are blanks, they can be neglected and you may work with a combination of stringformulas to find the individual parameters

Formulas in the example will work also in a script.

HTH Peter