topic Match not working in QlikView

Match not working

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Match not working

Wed, 09 Mar 2016 11:56:17 GMT

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')

Re: Match not working

martinpohl — Wed, 09 Mar 2016 12:06:43 GMT

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

Re: Match not working

Kushal_Chawda — Wed, 09 Mar 2016 12:11:21 GMT

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;

Re: Match not working

tresB — Wed, 09 Mar 2016 12:19:56 GMT

There could be a space issue. Try like:

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

Re: Match not working

sunny_talwar — Wed, 09 Mar 2016 13:26:48 GMT

or this:

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