Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be parenthesis placed in wrong place
if(len(keepchar(left(Cust_No,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) = 2 and IsNum(right(Cust_No,6))),'0','1')
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/
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
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.
May be parenthesis placed in wrong place
if(len(keepchar(left(Cust_No,2),'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) = 2 and IsNum(right(Cust_No,6))),'0','1')
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.
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.
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?
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.