topic Re: Sorting expression in QlikView

Sorting expression

pawwy1415 — Thu, 19 Oct 2017 16:01:18 GMT

Hi,

I have below expression used in dimension and I want to do sorting

= pick(match(MetricID,27,31,394,398,222,224,360,364,368,372,1141,1143,154,156,160,162,213,215,380,382)

,'PBT','Provisions','PBT','Provisions','Income','Income','Costs','Costs','Costs','Costs','PAT','PAT',

'Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs')

sorting should be like

Income

Costs

Provisions

PBT

PAT

RWA

I used below expression in sorting..

= pick(match(MetricID,222,224,360,364,368,372,31,398,27,394,1141,1143,154,156,160,162,213,215,380,382)

,1,1,2,2,2,2,3,3,4,4,5,5,

6,6,6,6,6,6,6,6)

but it doesn't work.. it's come in below order

Total RWAs

Costs

income

Provisions

PBT

PAT

Could you please see if I am doing something wrong in sorting?

Re: Sorting expression

pawwy1415 — Thu, 19 Oct 2017 16:01:35 GMT

This is in qlik sense

Re: Sorting expression

sunny_talwar — Thu, 19 Oct 2017 16:07:16 GMT

What if you just use this

Match(Only({1} MetricID), 222,224,360,364,368,372,31,398,27,394,1141,1143,154,156,160,162,213,215,380,382)

Re: Sorting expression

effinty2112 — Thu, 19 Oct 2017 16:08:08 GMT

Hi,

Try this as your sort expression:

Match(

pick(match(MetricID,27,31,394,398,222,224,360,364,368,372,1141,1143,154,156,160,162,213,215,380,382)

,'PBT','Provisions','PBT','Provisions','Income','Income','Costs','Costs','Costs','Costs','PAT','PAT',

'Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs')

,'*Income*','*Costs*','*Provisions*','*PBT*','*PAT*','*RWA*')

Good luck

Andrew

Re: Sorting expression

Anil_Babu_Samineni — Thu, 19 Oct 2017 16:08:45 GMT

Try this?

Match(MetricID, 'Total RWAs', 'Costs', 'income', 'Provisions', 'PBT', 'PAT')

Re: Sorting expression

pawwy1415 — Fri, 20 Oct 2017 08:50:31 GMT

Hi all,

tried all but none of them working

Re: Sorting expression

effinty2112 — Fri, 20 Oct 2017 09:13:44 GMT

Hi,

I had a typo, try:

WildMatch(

pick(match(MetricID,27,31,394,398,222,224,360,364,368,372,1141,1143,154,156,160,162,213,215,380,382)

,'PBT','Provisions','PBT','Provisions','Income','Income','Costs','Costs','Costs','Costs','PAT','PAT',

'Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs')

,'*Income*','*Costs*','*Provisions*','*PBT*','*PAT*','*RWA*')

Re: Sorting expression

pawwy1415 — Fri, 20 Oct 2017 10:11:22 GMT

it's coming in this order now..still not working

Re: Sorting expression

sunny_talwar — Fri, 20 Oct 2017 11:11:51 GMT

May be give this a short

=Match(

Pick(

Match(Only({1} MetricID),27,31,394,398,222,224,360,364,368,372,1141,1143,154,156,160,162,213,215,380,382),

'PBT', 'Provisions', 'PBT', 'Provisions', 'Income', 'Income', 'Costs', 'Costs', 'Costs', 'Costs', 'PAT', 'PAT', 'Total RWAs', 'Total RWAs', 'Total RWAs', 'Total RWAs', 'Total RWAs', 'Total RWAs', 'Total RWAs', 'Total RWAs'),

'Total RWAs', 'Costs', 'Income', 'Provisions', 'PBT', 'PAT')

Re: Sorting expression

pawwy1415 — Fri, 20 Oct 2017 11:21:34 GMT

it's coming this one..

Re: Sorting expression

sunny_talwar — Fri, 20 Oct 2017 11:24:28 GMT

Create this as an expression and share an image of your whatever you have as dimension and this expression

Re: Sorting expression

pawwy1415 — Fri, 20 Oct 2017 11:31:18 GMT

this is used as expression >> it's calculated dimensions

= pick(match(MetricID,27,31,394,398,222,224,360,364,368,372,1141,1143,154,156,160,162,213,215,380,382)

,'PBT','Provisions','PBT','Provisions','Income','Income','Costs','Costs','Costs','Costs','PAT','PAT',

'Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs','Total RWAs')

all others are measure

Re: Sorting expression

sunny_talwar — Fri, 20 Oct 2017 11:33:10 GMT

That is fine... use that as your dimension and this as your expression and post a screenshot... I don't need to see any other measure

=Match(

Pick(

Match(Only({1} MetricID),27,31,394,398,222,224,360,364,368,372,1141,1143,154,156,160,162,213,215,380,382),

'Total RWAs', 'Costs', 'Income', 'Provisions', 'PBT', 'PAT')

Re: Sorting expression

pradosh_thakur — Fri, 20 Oct 2017 12:07:40 GMT

please refer a sample which i have attached here for similar problem.

i have used dual function. hopefully it will help.

regards

Pradosh

Re: Sorting expression

pawwy1415 — Fri, 20 Oct 2017 13:20:19 GMT

there is no sample attached..

Re: Sorting expression

pawwy1415 — Fri, 20 Oct 2017 13:23:13 GMT

I can't use this as Dimension it's showing invalid dimesions..I was adding in sorting'expression;

Re: Sorting expression

pawwy1415 — Fri, 20 Oct 2017 13:24:09 GMT

Re: Sorting expression

sunny_talwar — Fri, 20 Oct 2017 13:25:13 GMT

I asked you to continue using your current dimension my friend... I just want you to add my expression as a measure... does it make sense now?

Re: Sorting expression

pradosh_thakur — Fri, 20 Oct 2017 13:26:48 GMT

i have attached the sample. if you look through inbox you wont see it .

Try this then

dimesnsion

= pick(match(MetricID,27,31,394,398,222,224,360,364,368,372,1141,1143,154,156,160,162,213,215,380,382),dual('PBT',5),dual('Provisions',4),dual('PBT',5),dual('Provisions',4),dual('Income',3),dual('Income',3),dual('Costs',2),dual('Costs',2),dual('Costs',2),dual('Costs',2),dual('PAT',6),dual('PAT',6),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1))

sorting expression

num(pick(match(MetricID,27,31,394,398,222,224,360,364,368,372,1141,1143,154,156,160,162,213,215,380,382),dual('PBT',5),dual('Provisions',4),dual('PBT',5),dual('Provisions',4),dual('Income',3),dual('Income',3),dual('Costs',2),dual('Costs',2),dual('Costs',2),dual('Costs',2),dual('PAT',6),dual('PAT',6),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1),dual('Total RWAs',1)))

regards

Pradosh

Re: Sorting expression

pawwy1415 — Fri, 20 Oct 2017 13:48:14 GMT

it's showing order as per attached screenshot..

but we need below sorting order

Income

Costs

Provisions

PBT

PAT

RWA