Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!?
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
If the plant name is supposed to be the part between the two underscores try:
purgechar(subfield(Dateiname, '_',2), ' ')
or
purgechar(textbetween(Dateiname, '_','_'), ' ')
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.
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