topic Check string format pattern in QlikView

Check string format pattern

Black_Hole — Sat, 16 Nov 2024 03:43:56 GMT

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.

Re: Check string format pattern

Anil_Babu_Samineni — Fri, 26 Apr 2019 17:55:14 GMT

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/

Re: Check string format pattern

rwunderlich — Sun, 28 Apr 2019 08:55:55 GMT

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

Re: Check string format pattern

Black_Hole — Mon, 29 Apr 2019 08:33:26 GMT

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.

Re: Check string format pattern

Anil_Babu_Samineni — Mon, 29 Apr 2019 13:05:17 GMT

May be parenthesis placed in wrong place

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

Re: Check string format pattern

Black_Hole — Mon, 29 Apr 2019 15:44:50 GMT

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.

Re: Check string format pattern

Black_Hole — Mon, 29 Apr 2019 16:02:21 GMT

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.

Re: Check string format pattern

Anil_Babu_Samineni — Mon, 29 Apr 2019 16:07:15 GMT

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?

Re: Check string format pattern

Black_Hole — Tue, 30 Apr 2019 09:39:21 GMT

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.