topic Re: Match in QlikView

Match

johnan — Thu, 02 Nov 2017 07:12:34 GMT

Hi,

I have this:

1	2	3	4

500936	27371385	ABI	AL
500936	27371389	ORD	AL
500936	27371405	BUF	FS
500936	27371415	ORD	FS
500936	27371437	ORD	FS
500936	27371469	ORD	AL
500936	27371494	ORD	AL
500936	27371499	ORD	PK
500936	27371512	ORD	PK
500936	27371514	ORD	PK
500936	27371552	FLA	PK

Ok, i just wanna have one box that showing if column 1 (order) just contains from column 3 FLA or /and ABI

I have this in my box:

=if (not match(Column 3, 'ORD','BUF'),Column 1)

This not working, if there is both ABI and FLA on the same order (Column 1) it will not show it just showing FLA OR ABI not the both if there is. What can i do?

Re: Match

tresB — Thu, 02 Nov 2017 07:17:00 GMT

Which object are you trying with? For multiple values try like:

=Concat ( Distinct (if (not match([Column 3], 'ORD','BUF'), [Column 1])) , ',')

Re: Match

johnan — Thu, 02 Nov 2017 07:18:59 GMT

Just a straight table in diagram.

Re: Match

prma7799 — Thu, 02 Nov 2017 07:24:13 GMT

if(not match(col3,'ORD','BUF'),col1) it is perfect please check your column spelling

Re: Match

johnan — Thu, 02 Nov 2017 07:28:16 GMT

Hi, i'ts not because it wont show if column 3 have both ABI and FLA on same number from Column 1

Re: Match

prma7799 — Thu, 02 Nov 2017 07:31:17 GMT

Like this you want or please share expected output ..

Re: Match

tresB — Thu, 02 Nov 2017 07:33:10 GMT

Have you tried with concat() as I showed above?

Re: Match

johnan — Thu, 02 Nov 2017 07:50:35 GMT

Sorry, i was not clear

1	2	3	4

500936	27371385	ORD	AL
500936	27371389	ORD	AL
500936	27371405	BUF	FS
500936	27371415	ORD	FS
500936	27371437	ORD	FS
500936	27371469	ORD	AL
500936	27371494	ORD	AL
500936	27371499	ORD	PK
500936	27371512	ORD	PK
500936	27371514	ORD	PK
500936	27371552	FLA	PK
500956	27371567	FLA	PK
500956	27371568	FLA	PK
500956	27371569	ABI	PK

I just wanna se order 500956 because it's just contains FLA and ABI

The order 500936 can be ignored i'ts contains ORD and it's not relevant for me.

Just when a Order only contains FLA or and ABI

Re: Match

johnan — Thu, 02 Nov 2017 07:50:43 GMT

Sorry, i was not clear

1	2	3	4

500936	27371385	ORD	AL
500936	27371389	ORD	AL
500936	27371405	BUF	FS
500936	27371415	ORD	FS
500936	27371437	ORD	FS
500936	27371469	ORD	AL
500936	27371494	ORD	AL
500936	27371499	ORD	PK
500936	27371512	ORD	PK
500936	27371514	ORD	PK
500936	27371552	FLA	PK
500956	27371567	FLA	PK
500956	27371568	FLA	PK
500956	27371569	ABI	PK

I just wanna se order 500956 because it's just contains FLA and ABI

The order 500936 can be ignored i'ts contains ORD and it's not relevant for me.

Just when a Order only contains FLA or and ABI

Re: Match

tresB — Thu, 02 Nov 2017 07:55:19 GMT

Try:

=Concat ( Distinct {<[Column 3]={'FLA','ABI'}>} [Column 1]) , ',')

Re: Match

johnan — Thu, 02 Nov 2017 08:26:02 GMT

Sorry, the order 501363 it's contains ORD

Re: Match

tresB — Thu, 02 Nov 2017 09:13:15 GMT

So you mean -those orders that contain 'FLA', 'ABI' and Not 'ORD','BUF' ? Try like:

=Concat ( Distinct {<[Column 3]={'FLA','ABI'} - {'ORD','BUF'}>} [Column 1]) , ',')

Re: Match

johnan — Thu, 02 Nov 2017 09:23:47 GMT

Sorry mate, doesn't work, it's still shows orders that contains ORD,BUF

I mean, those orders that's contains Only ABI or only FLA or ABI and FLA

Re: Match

tresB — Thu, 02 Nov 2017 09:36:28 GMT

Yes. I am sorry, the expression needs to be corrected. Try:

Concat(DISTINCT {<[Column1]=P({<[Column3]={'FLA','ABI'}>})>-<[Column1]=p({<[Column3]={'ORD','BUF'}>})>}[Column1], ',')

PFA

Re: Match

johnan — Thu, 02 Nov 2017 09:59:53 GMT

Mate..THANX