- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
match functions not working
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"'
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"'
];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 " " ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.