Match not working

[#1]

Hi,

I have issue with the not match below :

Part of the script to filter out TrasnportMode = A and V as below :

[StatusC]:

load OMSPO#,

MIN(date(TransmDate)) as TransmDateC ,

MIN(date(StatusDate)) as StatusDateC ,

MIN(date(StatusDate) )- MIN(date(TransmDate) )as DayDiffC

//if (min(date(StatusDate)) - min(date(TransmDate))<= '2','Y','N') as TargetC

resident [Shipment]

WHERE not match(TransportMode,'B','V') AND  trackCode ='BOOKCONFRE'

Group by OMSPO#

Order by StatusDate,TransmDate;

The result still how V in TransportMode but the trackCode filter worked.

OMSPO#	TransmDate	trackCode	statusCode	TransportMode	StatusDateC
5NG097	11/12/2015	BOOKCONFRE	400	V	-
5NG100	02/12/2015	BOOKCONFRE	400	V	-
5NG101	02/12/2015	BOOKCONFRE	400	V	-
5NG102	02/12/2015	BOOKCONFRE	400	V	-
5NG109	11/12/2015	BOOKCONFRE	400	V	-
5NG110	11/12/2015	BOOKCONFRE	400	V	-
15N042	02/12/2015	BOOKCONFRE	400	V	-
15N043	09/12/2015	BOOKCONFRE	400	V	-

Appreciate anyone could help.

Thank you.

Andros_Goh

[#2]

Hi Andros

try this with small change

i think you need to write like this

WHERE not match(TransportMode,'B','V') AND  match(trackCode ,'BOOKCONFRE')

[martinpohl]

Hello,

what is the data structure after load?

Did you drop the Shipment table?

The tablebox shows all fields from all tables.

It seems ok that the fields

OMSPO#

TransmDate

trackCode

statusCode

TransportMode

are in table Shipment and there are no StatusDateC because they don't match.

Regards

[Kushal_Chawda]

try this

[StatusC]:

load OMSPO#,

MIN(date(TransmDate)) as TransmDateC ,

MIN(date(StatusDate)) as StatusDateC ,

MIN(date(StatusDate) )- MIN(date(TransmDate) )as DayDiffC

//if (min(date(StatusDate)) - min(date(TransmDate))<= '2','Y','N') as TargetC

resident [Shipment]

WHERE not match(lower(trim(TransportMode)),'b','v') AND  trackCode ='BOOKCONFRE'

Group by OMSPO#

Order by StatusDate,TransmDate;

[tresesco]

There could be a space issue. Try like:

WHERE not match(Trim(TransportMode),'B','V') AND  trackCode ='BOOKCONFRE'

[sunny_talwar]

or this:

Where not WildMatch(TransportMode,'*B*','*V*') and WildMatch(trackCode, '*BOOKCONFRE*';