topic Re: Regarding Pivot Sorting in QlikView

Regarding Pivot Sorting

d_koti24 — Tue, 21 Jul 2015 09:38:09 GMT

Hi All,

I Have pivot table like this:

sample_table:

ENV DIR SIZES(gb):

RAP RAP_USER 200

RAP_TUSER 300

DEV .... ....

UAT .... ....

PROD PROD ENV 150

LIKE this.

my req is i want to sort like this

in env fied:

DEV

UAT

RAP

PROD

How to achive this?

Regards,

Re: Regarding Pivot Sorting

robert_mika — Tue, 21 Jul 2015 09:50:36 GMT

cretae inline stat,ent in script

CusotmENV:

LOAD * INLINE

[
Line, Order
DEV,1

UAT,2

RAP,3

PROD,4
];

Then use this Order as Sort By Expression in your PT

Re: Regarding Pivot Sorting

d_koti24 — Tue, 21 Jul 2015 09:58:14 GMT

Hi Robert ,

Can you please send me the sample app.

regards,

Re: Regarding Pivot Sorting

robert_mika — Tue, 21 Jul 2015 10:06:12 GMT

Feeling Qlikngry?

How To /Missing Manual(18 articles

Re: Regarding Pivot Sorting

settu_periasamy — Tue, 21 Jul 2015 10:19:20 GMT

Hi,

One more solution, if you want to do this in Expression Level, try this

=Match(Line,'DEV','UAT','RAP','PROD')

Re: Regarding Pivot Sorting

robert_mika — Tue, 21 Jul 2015 10:25:39 GMT

Good solution but if you got more values or your data set is quite large the script way will be faster.

Re: Regarding Pivot Sorting

d_koti24 — Tue, 21 Jul 2015 12:14:24 GMT

Thanks for quick reply,

is there any chance with out using inline table.

my pivot table contains 3 dimensions and 4 exp.

regards,

Re: Regarding Pivot Sorting

settu_periasamy — Tue, 21 Jul 2015 12:19:11 GMT

Hi,

did you try the match function (see my previous post)?

Re: Regarding Pivot Sorting

d_koti24 — Tue, 21 Jul 2015 12:30:14 GMT

ya.but i did nt get.

=IF(DIR='abDASHBOARDS','PROD',

IF(DIR='fgfgfgf','UAT',

IF(DIR='BIfgfg','PROD',

IF(DIR='sample','UAT',

IF(DIR='dfdfd','DEV',

IF(DIR='fdfd\AP','PROD',

IF(DIR='\\ssss','RAP_ARCH',

IF(DIR='\\aaa\dfdfd','RAP_ARCH',

IF(DIR='\\fff\fffffffff','RAP_ARCH')))))))))

this is my envr dimension.based on dir we are given that envr names.

i want to sort the pivot table based on first dimension. envr is the first dimension.

Re: Regarding Pivot Sorting

robert_mika — Tue, 21 Jul 2015 12:34:27 GMT

Could you move this calculated dimension to script?

This will speed up your calculation.

And then use the Match function?

Re: Regarding Pivot Sorting

Colin-Albert — Tue, 21 Jul 2015 12:34:46 GMT

If you define the data as a DUAL value, then the sort will be applied automatically.

CustomENV:

LOAD

Dual( Line, Order) as Line

inline [
Line, Order
DEV,1

UAT,2

RAP,3

PROD,4
];

Re: Regarding Pivot Sorting

veidlburkhard — Tue, 21 Jul 2015 12:56:37 GMT

Hi koti,

in order to sort ENV go to the 'Sort' Tab and sort ENV by Expression

=Match(ENV, 'DEV', 'UAT', 'RAP', 'PROD')

That's all.

Regards

Burkhard

Re: Regarding Pivot Sorting

d_koti24 — Tue, 21 Jul 2015 13:09:46 GMT

=match(Environment, 'DEV','UAT', 'RAP_ARCH','PROD')

I was tried,but i did nt get.

Re: Regarding Pivot Sorting

veidlburkhard — Tue, 21 Jul 2015 13:18:31 GMT

Why don't you send us a screenshot of the sort tab so we can check and correct...?

Re: Regarding Pivot Sorting

d_koti24 — Tue, 21 Jul 2015 14:02:50 GMT

Hi All,

Find the sample app.

i want to sort the 1st dimension ,

like dev,uat,rap,prod. in front end only.

regards,

kd !

Re: Regarding Pivot Sorting

veidlburkhard — Tue, 21 Jul 2015 14:35:40 GMT

Now it is clear, koti.

In the script you have to create a field envr like this:

Then you can use this field as your dimension in the pivot chart instead of your calculated dimension.

The sorting is done very easily now with the match funktion:

Regards

Burkhard

Re: Regarding Pivot Sorting

robert_mika — Tue, 21 Jul 2015 14:39:52 GMT

=if(match(Dir,' \\abghg\DEV','\\deh\DEV1','\\fgfgd\DATA','\\dgfgfgf\GM0100','sampleDEV01'),dual('DEV',1),

if(match(Dir,'\\MW1010\12000','\\jhjhjhjK\GMW','\\SDV064\GMTQT0101'),dual('RAP_ARCH',3),

if(match(Dir,'\\NA0063\GMTQT0099','DASHBOARDS','GBI','DASHBOARDSC'),Dual('PROD',4),

if(match(Dir,'DASHBOARDSUAT','GBIUAT'),Dual('UAT',2),dual('',5)))))

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Re: Regarding Pivot Sorting

d_koti24 — Tue, 21 Jul 2015 14:43:04 GMT

With out createing envr in script level,is there any chance to do in sort exp.

regards,

Re: Regarding Pivot Sorting

robert_mika — Tue, 21 Jul 2015 14:50:48 GMT

See my post below

Re: Regarding Pivot Sorting

d_koti24 — Tue, 21 Jul 2015 15:13:12 GMT

Thanks alot Robert.

regards.