topic Re: Pivot Table - Sorting in QlikView

Pivot Table - Sorting

mee-qlik — Mon, 17 Sep 2018 13:29:54 GMT

Hi,

I have some financial data with a hierarchy on “cod voce” dimension ( see “Detail” tab in the example file).

I drew a list box with option “Show as treeView” and sorting by “cod voce” . Works perfectly.

Then I drew a pivot table with the same sorting, but sorting don’t work ( see attached file) even if I use the same sorting criteria.

For example see the position of “Total” in the first picture (correct) and in the second picture (wrong).

I tried same other criteria, unsuccessfully.

Can you help me?

Re: Pivot Table - Sorting

Anil_Babu_Samineni — Mon, 17 Sep 2018 13:36:56 GMT

PFA

Re: Pivot Table - Sorting

mee-qlik — Mon, 17 Sep 2018 14:05:12 GMT

Thank you Anil,

but with your solution only the "total" go to the right position, but other record no

My goal in pivot table is to respect the order as in the first picture (treeview)

Re: Pivot Table - Sorting

Anil_Babu_Samineni — Tue, 18 Sep 2018 02:58:08 GMT

Check Now? I prefer to work same sequential order in script instead UI to make dynamic..

Re: Pivot Table - Sorting

mee-qlik — Tue, 18 Sep 2018 12:11:54 GMT

Thank you again Anil.

Your function (match) work only in this particulary case.

But goal is sort ALL the data by "cod voce" or, if you prefer numerico dimension in sequential order, "num_riga_saldi_cee".

I try even with this dimension, but it don't work.

Re: Pivot Table - Sorting

Anil_Babu_Samineni — Wed, 19 Sep 2018 00:35:52 GMT

I didn't say this will work fully, but partially it should work. And that is why i commented to manipulate in script the same order..

The reason i am saying is either it will work A-Z string pattern and number format.. if you need certain order one option like mine earlier and another one is script..

Re: Pivot Table - Sorting

mee-qlik — Wed, 19 Sep 2018 08:25:55 GMT

Thank you again Anil,

but in the script, if you prefer to work with a progressive number, is already present a dimension numeric progressive as you can see in tab "detail". I try to use it sorting the pivot table but it doesn' t work.

Re: Pivot Table - Sorting

Anil_Babu_Samineni — Mon, 08 Oct 2018 13:57:14 GMT

I have attached which can be done, Is that issue still persist?

Re: Pivot Table - Sorting

captain89 — Mon, 08 Oct 2018 16:36:04 GMT

Hi Enrico,

I solved the same problem with sql.

In this way you get the right total in the right row:

SELECT

A.cod_bilcee_mod AS cod_bilcee_mod,

A.cod_voce AS cod_voce,

A.cod_voce AS cod_voce_padre

FROM [CA_BC_VOCI] A

UNION

SELECT

A.cod_bilcee_mod AS cod_bilcee_mod,

A.cod_voce AS cod_voce,

A.cod_voce_padre AS cod_voce_padre

FROM [CA_BC_VOCI] A

UNION

SELECT

A.cod_bilcee_mod AS cod_bilcee_mod,

A.cod_voce AS cod_voce,

B.cod_voce_padre AS cod_voce_padre

FROM [CA_BC_VOCI] A

LEFT JOIN [CA_BC_VOCI] B ON B.cod_bilcee_mod=A.cod_bilcee_mod AND B.cod_voce=A.cod_voce_padre

WHERE B.cod_voce_padre<>0

UNION

SELECT

A.cod_bilcee_mod AS cod_bilcee_mod,

A.cod_voce AS cod_voce,

C.cod_voce_padre AS cod_voce_padre

FROM [CA_BC_VOCI] A

LEFT JOIN [CA_BC_VOCI] B ON B.cod_bilcee_mod=A.cod_bilcee_mod AND B.cod_voce=A.cod_voce_padre

LEFT JOIN [CA_BC_VOCI] C ON C.cod_bilcee_mod=B.cod_bilcee_mod AND C.cod_voce=B.cod_voce_padre

WHERE C.cod_voce_padre<>0

UNION

SELECT

A.cod_bilcee_mod AS cod_bilcee_mod,

A.cod_voce AS cod_voce,

D.cod_voce_padre AS cod_voce_padre

FROM [CA_BC_VOCI] A

LEFT JOIN [CA_BC_VOCI] B ON B.cod_bilcee_mod=A.cod_bilcee_mod AND B.cod_voce=A.cod_voce_padre

LEFT JOIN [CA_BC_VOCI] C ON C.cod_bilcee_mod=B.cod_bilcee_mod AND C.cod_voce=B.cod_voce_padre

LEFT JOIN [CA_BC_VOCI] D ON D.cod_bilcee_mod=C.cod_bilcee_mod AND D.cod_voce=C.cod_voce_padre

WHERE D.cod_voce_padre<>0

UNION

SELECT

A.cod_bilcee_mod AS cod_bilcee_mod,

A.cod_voce AS cod_voce,

E.cod_voce_padre AS cod_voce_padre

FROM [CA_BC_VOCI] A

LEFT JOIN [CA_BC_VOCI] B ON B.cod_bilcee_mod=A.cod_bilcee_mod AND B.cod_voce=A.cod_voce_padre

LEFT JOIN [CA_BC_VOCI] C ON C.cod_bilcee_mod=B.cod_bilcee_mod AND C.cod_voce=B.cod_voce_padre

LEFT JOIN [CA_BC_VOCI] D ON D.cod_bilcee_mod=C.cod_bilcee_mod AND D.cod_voce=C.cod_voce_padre

LEFT JOIN [CA_BC_VOCI] E ON D.cod_bilcee_mod=D.cod_bilcee_mod AND E.cod_voce=D.cod_voce_padre

WHERE E.cod_voce_padre<>0

Re: Pivot Table - Sorting

mee-qlik — Tue, 09 Oct 2018 07:38:32 GMT

Hi Matteo,

thank you for your time. The problem isn't the totals ( the DB table of the financial program is done like an old cobol program, ther are the rows of detail and even the rows of totals)

My only problem is sorting the pivot table that i drew. My goal is sort the pivot table by "cod_voce" or "num_riga", how you prefer.

Thank you again