match functions not working

[diwaskarki]

My database field 'ELT' holds various format of strings.  I have four different wildmatch functions to extract the string I need. Below is my script and sample data for each match function:

If(WILDMATCH(ELT, 'groups ==*'), TextBetween(ELT, 'groups ==', ','),

If(WILDMATCH(ELT, 'sfAAIGroup ==*'), TextBetween(ELT, 'sfAAIGroup == "', '"'),

if (WILDMATCH(ELT, '0memberOf == "CN=*'), TextBetween(ELT, 'memberOf  == "CN=', ',' ),

if (WILDMATCH(ELT, 'groups == "cn=*'), TextBetween(ELT,'groups == "cn=', ','), ELT)))), As [RBAC Entitlements];

1st widlmatch sample data: groups == "C02618"

2nd wildmatch sample data:  SFAAIGROUP == "GA0401"

3rd wildmatch sample data:   memberOf == "CN=IIQ_TESTGROUP21_SLG,OU=Groups,OU=SAILPOINT,OU=C00826,OU=Service,DC=UNITOPR,DC=UNITINT,DC=TEST,DC=COMPANY,DC=ORG"

4TH wildmatch sample data: groups == "cn=IIQ_FPR_TP_9,ou=groups,ou=enterprise,o=company,c=us"

I tried both match and wildmatch but it does not bring anything. I would really appreciate any kind of help. The script loads fine but doesnt bring in any data.

[jwjackso · Accepted solution]

If this is your data, I would rearrange the test for "groups*".

Data:

LOAD *,

     If(WILDMATCH(ELT, 'groups == "cn*'), TextBetween(ELT, 'groups == "cn=', ','),

     If(WILDMATCH(ELT, 'sfAAIGroup ==*'), TextBetween(ELT, '"', '"'),

     if (WILDMATCH(ELT, 'memberOf == "CN=*'), TextBetween(ELT, 'memberOf == "CN=', ',' ),

     if (WILDMATCH(ELT, 'groups == "*'), TextBetween(ELT,'"', '"'), ELT)))) As [RBAC Entitlements];

LOAD * Inline [

ELT

'groups == "C02618"'

'SFAAIGROUP == "GA0401"'

'memberOf == "CN=IIQ_TESTGROUP21_SLG,OU=Groups,OU=SAILPOINT,OU=C00826,OU=Service,DC=UNITOPR,DC=UNITINT,DC=TEST,DC=COMPANY,DC=ORG"'

'groups == "cn=IIQ_FPR_TP_9,ou=groups,ou=enterprise,o=company,c=us"'

];

[diwaskarki]

Thanks Jerry, worked liked a charm.

Can you help me understand how the textbetween is working here? We define the pattern of strings we are looking for in the wildmatch. Are we only getting the textbetween the first set of "  " ?

[jwjackso]

The textbetween function (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/StringFunction...) is just returning the text between 2 delimiters.

In your original If test, the first test matched both the 'groups == "' and 'groups == "cn'.  Your test never reached the 'groups == "cn' test.

In the modified If test:

In the first test, the text is between the  delimiters 'groups == "cn=' and a comma.

In the second test, the text you wanted was between 2 double quotes.

In the third test, the text you wanted was between  'memberOf == "CN= and a comma.

In the fourth test, the text you wanted was between  2 double quotes.