Check string format pattern

[Black_Hole]

Hello all,

I would like to check the format of my values.

A good format is defined like "XX999999" where XX refers to the ISO country and 999999 is a number.

Previously, I worked in Excel and I used it a regex function (VBA) but I am not familiar with QV and I have a lot of difficulties with the script editor.

So, I would like to use expression, for now it is more easy for me to do it.

Please do you have any advice to define simply a format in expression (from Properties of an object).

Thank you in advance for your help.

[Anil_Babu_Samineni]

As this is Qlikview, There is no in-built functions or support for that. Perhaps try with Macros

https://community.qlik.com/t5/QlikView-Documents/How-to-use-regular-expressions/ta-p/1477236

http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

[rwunderlich]

You could do something like:

len(keepchar(left(str,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') = 2
and IsNum(right(str,6))

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Black_Hole]

Hello @Anil_Babu_Samineni, @rwunderlich, 

Thanks to both of you for your precious help.

@Anil_Babu_Samineni :  Thank you for sharing the links. In the first approach to resolve my request I thought immediately to regex. So, I implemented a macro in VBscript (this one) and then I called the function RegexTest in my Script Editor. Unfortunately, I didn't succeed. That's why, I have changed my approach to resolve my request. I hope that I can use a regex when I will be more familiar with QV.  

@rwunderlich : Your expression is exactly what I would like to do. So I try it in a conditional expression. But the result is always '0'.  Normally, I must obtain the value '1' if the condition is not verified and it's not the case in my table.

Below the table with result of the expression:

The expression of the table above:

if(len(keepchar(left(Cust_No,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') = 2 and IsNum(right(Cust_No,6))),'0','1')

Please could you tell what is wrong in my input.

Thank you in advance for your help.

[Anil_Babu_Samineni · Accepted solution]

May be parenthesis placed in wrong place

if(len(keepchar(left(Cust_No,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) = 2 and IsNum(right(Cust_No,6))),'0','1')

[Black_Hole]

Hi @Anil_Babu_Samineni ,

Great, I correct and also I add another condition in my expression. The function runs correctly.

Please could you advise me, I have different "if"  statements depending on the country of my customer.

I would like to know what is the best way in this context to develop multiple"IF" statements.

Should I prefer to develop the different statements in the expression property of my straight table or in the edit script ?

I feel a little uncomfortable with the script editor. I try the following code but it's a failed.

Below my code in the script editor and the message error that I received:

/**** SCRIPT ****/

CUST_CHECK:
LOAD
CUST_NO,
if(len(keepchar(left(CUST_NO,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ') = 2 and IsNum(right(CUST_NO,6)),'0','1') AS FLAG
FROM CUST_TABLE ;

/**** ERROR MESSAGE ****/

File not found error

Cannot open file: 'C:\Users\Desktop\MYSERVERNAME'
System error: Filesystem::FindImpl FindFirst call: Incorrect function: "C:\Users\Desktop"

****************************

Thank you in advance for your advice.

[Black_Hole]

Hi @Anil_Babu_Samineni,

Great, I corrected and I add another condition to fulfill my purpose. My expression runs right now.

Please could you advise me.

Indeed, I have multiple "If" statement, according to the country of my customer.

Should I prefer to develop the different statement in the "expression property" of my straight table or should I write my statement in edit script?
I try the following code in my edit script but it's a failed (below the error message):

CUST_CHECK:
LOAD
Cust_No,
if(len(keepchar(left(Cust_No,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) = 2 and IsNum(right(Cust_No,6)),'0','1') as FLAG
FROM Cust_Table;

/**** ERROR MESSAGE ****/

File not found error

Cannot open file: 'C:\Users\Desktop\MyserverName'
System error: Filesystem::FindImpl FindFirst call: Incorrect function: "C:\Users\Desktop"

CUST_CHECK:
LOAD
Cust_No,
if(len(keepchar(left(Cust_No,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) = 2 and IsNum(right(Cust_No,6)),'0','1') as FLAG
FROM Cust_Table;

Thank you in advance for your advice.

[Anil_Babu_Samineni]

First part, Preferably use in script as there is no complex to use. 

Second part, Did you gave Library path or simply loading the external drive?

[Black_Hole]

Hello @Anil_Babu_Samineni ,

Currently I use the free version downloaded via the website but in June I will obtain the licence.

So, I would like to develop a solution which runs correctly even if I pass from  free version to licence.

But I don't understand, is it because I have the free version that's why I cannot use the Load statement in my script?

In my script, I would like to run something like below, do you know if it's possible?

LOAD

Country, Cst_No,

If Country="GB" Then

....

If Country="ES" Then

...

FROM Cust_Table;

Thank for your help and for your time.