match / wildmatch not working

[swearengen]

Hi,

having issues getting match or wildmatch to work, but just in one place 😕

I have tried all the combinations below but only the final one returns any results:

stage_joint_1:
LOAD bussfld4_latest as bussfld4_stage,
1 as call_stage,
0 as ds_stage,
0 as acc_opened_stage
Resident joint1 where Wildmatch(call_outcm_cd_latest,'*First*')>0;

LOAD bussfld4_latest as bussfld4_stage,
1 as call_stage,
0 as ds_stage,
0 as acc_opened_stage
Resident joint1 where Wildmatch(call_outcm_cd_latest,'First*')>0;

LOAD bussfld4_latest as bussfld4_stage,
1 as call_stage,
0 as ds_stage,
0 as acc_opened_stage
Resident joint1 where Match(call_outcm_cd_latest,'First Call Attempt')>0;

LOAD bussfld4_latest as bussfld4_stage,
1 as call_stage,
0 as ds_stage,
0 as acc_opened_stage
Resident joint1 where Wildmatch(trim(call_outcm_cd_latest),'First*')>0;

LOAD bussfld4_latest as bussfld4_stage,
1 as call_stage,
0 as ds_stage,
0 as acc_opened_stage
Resident joint1;

Document log extract attached. Also attached list box of valid searchable items (including 'First Call Attempt')

In the same script the following code _does_ work so I'm totally at a loss.

LOAD
bussfld4 as bussfld4_latest,
Timestamp(APPLYMAP('map_acc_opened',ACC_NO,null())+time('23:59:59', 'hh:mm:ss'),'M/D/YYYY h:mm:ss.fff TT') as event_ts,
null() as time_taken,
Date(APPLYMAP('map_acc_opened',ACC_NO,null()),'M/D/YYYY') as event_date,
'Account Opened' as call_outcm_cd_latest,
ACC_NO as bus_outcm_latest,
APPLYMAP('map_acc_opener',ACC_NO,null()) AS staff_latest
Resident docusign WHERE WildMatch(ACC_NO,'009*')>=1;

Any insight from a fresh set of eyes much appreciated.

Thanks,

Stephen

[Or · Accepted solution]

This would appear to be something with your underlying data, because in isolation all of these work correctly:

Table:
Load 'First Call Attempt' as call_outcm_cd_latest Autogenerate(1);

Load Wildmatch(call_outcm_cd_latest,'*First*'),
Wildmatch(call_outcm_cd_latest,'First*'),
Match(call_outcm_cd_latest,'First Call Attempt'),
Wildmatch(trim(call_outcm_cd_latest),'First*')
Resident Table;

[swearengen]

Hey Or,

thanks for taking the time to look at it. It turns out I had a stray NoConcatenate when I was building the table.

joint1:
LOAD
bussfld4 as bussfld4_latest,
Timestamp(createdtime,'M/D/YYYY h:mm:ss.fff TT') as event_ts,
null() as time_taken,
Date(createdtime,'M/D/YYYY') as event_date,
'Enquiry Created' as call_outcm_cd_latest,
'Enquiry Created' as bus_outcm_latest,
'LCM' AS staff_latest
Resident test_header;

NoConcatenate LOAD
bussfld4 as bussfld4_latest,
Timestamp(CALL_DATE_1,'M/D/YYYY h:mm:ss.fff TT') as event_ts,
Interval(APPLYMAP('fc_delay',bussfld4,null()), 'DD hh mm ss') as time_taken,
Date(CALL_DATE_1,'M/D/YYYY') as event_date,
'First Call Attempt' as call_outcm_cd_latest,
BUS_OUTCM_1 as bus_outcm_latest,
STAFF_ID_1 AS staff_latest
Resident CAO_EDW_Query_rk4 where not(isnull(CALL_DATE_1));

So even though those records seemed to be resolving to joint1 when I added the 'First Call Attempt's they somehow weren't 🤔. I'm not sure where they came from when I did the LOAD with no where clause but then I'm a bit iffy on the way the whole thing hangs together. I miss SQL 😐

Anyways thanks very much for your help it got me looking in the right place.

[Or]

As a minor aside, you can just use e.g. Where WildMatch(Field,'String') without checking >0. It's cleaner to read that way, IMO.

[swearengen]

👍