Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
It might be possible to do this in a VB function, as you tried. I would however do it in the QlikView script, using several steps. Put the following code after your Sample table:
IncorrectNames:
Load * where not ( IsText(Left(SubName,1)) and IsNum(mid(SubName,2)) and Len(Trim(SubName))=3 );
Load
Name as IncorrectName,
mid(Name,iterno()*3-2,3) as SubName resident Sample
While iterno()<= 1+(Len(Name)-1)/3;
CorrectNames:
Noconcatenate Load * resident Sample Where not Exists(IncorrectName,Name);
Drop Tables Sample, IncorrectNames;
HIC
It might be possible to do this in a VB function, as you tried. I would however do it in the QlikView script, using several steps. Put the following code after your Sample table:
IncorrectNames:
Load * where not ( IsText(Left(SubName,1)) and IsNum(mid(SubName,2)) and Len(Trim(SubName))=3 );
Load
Name as IncorrectName,
mid(Name,iterno()*3-2,3) as SubName resident Sample
While iterno()<= 1+(Len(Name)-1)/3;
CorrectNames:
Noconcatenate Load * resident Sample Where not Exists(IncorrectName,Name);
Drop Tables Sample, IncorrectNames;
HIC
Thank you very much HIC!
It works!
An alternative (but not as elegant as Henric's!) solution is as follows ...
Set validChars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
Set validNums = '0123456789';
Pattern: // 4 levels
LOAD *
WHERE candidate1 = 123
and (candidate2 = 456 or candidate2 = 000)
and (candidate3 = 789 or candidate3 = 000)
and (candidate4 = 101112 or candidate4 = 000)
and (candidate5 = 000)
and len(Name) = len(NameExclInvalidChars);
LOAD
Name,
FINDONEOF(Name,'$(validChars)',1) & FINDONEOF(Name,'$(validNums)',1) & FINDONEOF(Name,'$(validNums)',2) as candidate1,
FINDONEOF(Name,'$(validChars)',2) & FINDONEOF(Name,'$(validNums)',3) & FINDONEOF(Name,'$(validNums)',4) as candidate2,
FINDONEOF(Name,'$(validChars)',3) & FINDONEOF(Name,'$(validNums)',5) & FINDONEOF(Name,'$(validNums)',6) as candidate3,
FINDONEOF(Name,'$(validChars)',4) & FINDONEOF(Name,'$(validNums)',7) & FINDONEOF(Name,'$(validNums)',8) as candidate4,
FINDONEOF(Name,'$(validChars)',5) & FINDONEOF(Name,'$(validNums)',9) & FINDONEOF(Name,'$(validNums)',10) as candidate5,
KeepChar(Name,'$(validChars)' & '$(validNums)') as NameExclInvalidChars
resident Sample;
This might however offer a solution if the pattern is a bit less repetitive such as XnnXnXnX etc.
flipside
Thank you flipside, this is an good solution, too.
Hello Henric Cronström,
one question. I want to modify your expression for the patterns
Xnn
XnnXnn
XnnXnnXn (my first example was XnnXnnXnn).
How can i do this in your code?
Thanks for your help!
Best Regards,
Aylin
When I filter out the incorrect SubNames, I used
Load * where not ( IsText(Left(SubName,1)) and IsNum(mid(SubName,2)) and Len(Trim(SubName))=3 );
i.e. I demand that a SubName must have
If you also want to accept 'XnnXnnXn', you could just modify the third demand to Len(Trim(SubName))>=2. But then you will also accept 'XnnXn Xnn', i.e. a string where you have a space instead of a digit in the third position. But maybe this is not a problem? If so,
Load * where not ( IsText(Left(SubName,1)) and IsNum(mid(SubName,2)) and Len(Trim(SubName))>=2 );
If it is a problem, you need to modify your Load statement further, and perhaps use PurgeChar(Name,' ') in the mid() function and in the while loop to remove internal blanks.
HIC
Thank you very much.
I reload the "Correct Names" Table and use the left-Function to cut only the Names, which are exactly 8 characters. So it works with your help! Thank you again!