topic Re: Match() in QlikView

Match()

nihhalmca — Fri, 07 Mar 2014 08:34:26 GMT

Hi All,

I have to sort Expense type field, it contains many number of values.

For example: values are A to Z.

I want to make order sequence like Z,A,L and remaining values are same order.

This one should do only in UI design side ( not by Apply-map).

Please help me on it.

Thanks,

NiHhal.

Re: Match()

arulsettu — Fri, 07 Mar 2014 08:40:42 GMT

try if(match(Expense,'Z','A','L'),Expense)

hope it helps.....

Re: Match()

Fri, 07 Mar 2014 08:41:09 GMT

Can you try this in the UI ?

Express sort order:

match(ExpenseType,'L,' 'A', 'Z')

so that Z will get 3, A will get 2, and L will get 1

the other will not be found and will get 0

Fabrice

Re: Match()

arulsettu — Fri, 07 Mar 2014 08:42:05 GMT

or in properties SORT tab text Z-A.. try that...

Re: Match()

stevedark — Fri, 07 Mar 2014 08:49:29 GMT

To add to AUNEZ FABRICE this will then leave the rest of the values in a random order, or if you text the Text as well as the Expression sort it should pick up, but I would look at ensuring the expression delivers the right sort order.

The sort expression would then be something like:

=if(match(ExpenseType,'L,' 'A', 'Z') = 0, '9', match(ExpenseType,'L,' 'A', 'Z')) & ExpenseType

This would put a single digit in front of the Expense type for the sort order of 1,2,3 or 9, the rest of the Expense type is then used for the rest of the sort.

It's a shame you can't do anything in the load script, as this would be much more performant and robust. You could do it by making expense type a dual data type (ie. it has a numeric representation as well as a text one).

Re: Match()

jagan — Fri, 07 Mar 2014 09:00:33 GMT

Hi,

If values are more then it is difficult to manage in Match function, instead you can create one Inline/Excel table like this

LOAD

INLINE [

Expense type, SortOrder

Z, 1

A, 2

L, 3

];

Now in sort tab just SortOrder. Hope this helps you.

Regards,

Jagan.

Re: Match()

SunilChauhan — Fri, 07 Mar 2014 09:15:33 GMT

Try Below

Chart properties-> short Expression

wildmatch( FieldName,'Z','A','L',Fieldname)

Hope this helps

Re: Match()

IAMDV — Fri, 07 Mar 2014 12:29:04 GMT

Hi Nihhal,

I've made a video tutorial on Match() function. Here is the link to my blog (Copy & Paste):

http://qlikshare.com/292

Hope it helps!

Cheers,

Re: Match()

maxgro — Fri, 07 Mar 2014 22:09:36 GMT

in sort tab of listbox Expense

- flag expression ascending

- flag text ascending

-in expression use wildmatch(Expense, 'Z', 'A', 'L', '*')

to sort in that order: Z A L B C D ......

wildmatch sorts first Z A L; the other chars (* in wildmatch) are sorted by text order (you flag text)

Re: Match()

nihhalmca — Mon, 24 Mar 2014 11:07:29 GMT

Thanks for reply,

Its working some what not up to mark, after little changes

match(Expense,'Z','A','L',Expense)