topic Re: Table customization in QlikView

Table customization

Anonymous — Wed, 22 Nov 2017 10:37:44 GMT

Good Morning,

I have created table in which My table shows the Top 15 client trends and remaining as Others.I just wanted to move the Unknown & Competion IBG from the Top 15 and add to the Others irrespective of the RTA$(shown below).Please suggest?

Rollover Destination	RTA $
FI	$176,368,433
CH	$49,327,118
VAN	$44,383,002
MOR	$30,092,771
UNKNOWN	$22,230,271
BAN	$21,506,932
AME	$21,052,990
EDW	$17,325,477
MERR	$16,351,848
AMER	$14,120,564
WELLS	$12,825,707
COMPETITION - IBG	$12,237,331
LINS	$8,709,050
UBS	$6,851,787
Ohters	$71,750,223

Re: Table customization

petter — Wed, 22 Nov 2017 11:06:55 GMT

How did you create your table? In the load script or as a straight table?

Re: Table customization

Anonymous — Wed, 22 Nov 2017 11:09:39 GMT

Hi peter,

I have loaded the script to get the those table fields....

Re: Table customization

petter — Wed, 22 Nov 2017 11:18:18 GMT

You can do a resident load like this:

FINALTABLE:

LOAD

If( Match( [Rollover Destination] ,'UNKNOWN' ,'COMPETITION - IBG' ) , 'Others' , [Rollover Destination] ) AS [Rollover Destination],

[RTA $]

RESIDENT

[SOURCETABLE];

You will have three rows in the FINALTABLE with 'Others' but they will sum up to one row in a Straight Table or a Pivot Table later in your UI.

If you want to sum the three rows in the load script you could do this:

FINALTABLE:

LOAD

[Rollover Destination],

Sum( [RTA $]

GROUP BY

[Rollover Destination]

;

LOAD

If( Match( [Rollover Destination] ,'UNKNOWN' ,'COMPETITION - IBG' ) , 'Others' , [Rollover Destination] ) AS [Rollover Destination],

[RTA $]

RESIDENT

[SOURCETABLE];

Re: Table customization

avkeep01 — Wed, 22 Nov 2017 11:29:17 GMT

Hi Surya,

in the chart make the first expression as follows:

SUM(AGGR(IF(MATCH([Rollover Destination],'COMPETITION - IBG','UNKNOWN'), RANK(SUM([RTA $]),4)+15,RANK(SUM([RTA $]),4)),[Rollover Destination]))

you can make the + 15 variable btw.

then make the seconde expression SUM([RTA $]).

on the presentation tab in the chart you can hide the first expression.

see my attachment for the example. I didn't hide the second expression yet.

Re: Table customization

woshua5550 — Wed, 22 Nov 2017 11:30:25 GMT

if you prefer to deal with it at frontend , you can try expression like this

IF([Rollover Destination] = 'Others',Sum(TOTAL {<[Rollover Destination] = {'UNKNOWN','COMPETITION - IBG','Others'}>}[RTA $]), Sum({<[Rollover Destination] =- {'UNKNOWN','COMPETITION - IBG'}>}[RTA $]))

PFA

Re: Table customization

Anonymous — Wed, 22 Nov 2017 11:38:35 GMT

Hi Petter,

I am not looking to create 'Others' category in table.In the UI, the below option which is available in the Table.

There i wanted to add 'UNKNOWN' ,'COMPETITION - IBG' to Other defult category.please suggest?

Re: Table customization

Anonymous — Wed, 22 Nov 2017 11:42:17 GMT

Hi Dave,

The above expression removes the value associated to this UNKNOWN','COMPETITION - IBG categories .

But i want those values to be added to the Others category.

Re: Table customization

Anonymous — Wed, 22 Nov 2017 11:42:52 GMT

Hi Van

Can you please explain more....

the above expression its not working out

Re: Table customization

woshua5550 — Wed, 22 Nov 2017 11:53:48 GMT

have you checked attachment ? it looks work as your wish

Re: Table customization

Anonymous — Wed, 22 Nov 2017 11:56:42 GMT

Hi Dav,

can u share the attachment again? becoz i am not see any attached file

Re: Table customization

woshua5550 — Wed, 22 Nov 2017 12:02:42 GMT

that probably because you were reading message in your inbox rather than in our discussion page , try open this

Re: Table customization

avkeep01 — Wed, 22 Nov 2017 12:31:10 GMT

Hi Surya,

I made a straight tabel. I set [Rollover Destination] as dimension. The first expression is a rank over the [RTA $].

The Expression is build of the following components:

1. RANK(SUM([RTA $]),4)) -> the ,4 makes the rank following up on the previous row in case the values are the same. I.e. if the first value equals the second then with ,4 you set them as 1 and 2, otherwise you would have 1 for both of the values.

2. Then I made an if statement where I define the [Rollover Destinations] that needs to be excluded:

IF(MATCH([Rollover Destination],'COMPETITION - IBG','UNKNOWN'), <true>, <false>

When the [Rollover Destionation] = competition ibg or unknown then I add +15 the the rank of the [Rollover destiniation]. I choose + 15 because then they will drop of the first 15 destinations you want to see. You can set the number higher if you want.

3. Next step is aggregating the data per [Rollover Destination] and I need to have a number per destionation. so I used SUM(AGGR(<expression>,[Rollover Destination])). I also could have used MAX or MIN or Only. I just needed a function generating 1 number per destinations.

The [Dimension Limits] looks at the first expression. So setting the first Dimension limit showing only the first 15 values makes competition ibg and unknown appearing under "others".

Then you don't want to see the rank expression, so you hide the column at the presentation tab.

Can you tell me what isn't working out in your application?

Re: Table customization

avkeep01 — Wed, 22 Nov 2017 12:36:58 GMT

I made the example in Qlikview. I think you were working with Sense. I'll need to adjust the solution for Sense.

Re: Table customization

avkeep01 — Wed, 22 Nov 2017 12:41:40 GMT

Hi Surya,

My expression works when you select "Bottom" instead of "Top". But you can't hide columns in Sense

Re: Table customization

Anonymous — Wed, 22 Nov 2017 12:49:36 GMT

Hi Dav

Your solution is removing the RTA$ value from the entire table output....After using your logic i am finding those difference in the data and that is equal to the Others category

Rollover Destination

RTA $

Rollover Destination

RTA $

$176,368,433

$49,327,118

VAN

$44,383,002

VAN

$44,383,002

MOR

$30,092,771

MOR

$30,092,771

UNKNOWN

$22,230,271

BAN

$21,506,932

BAN

$21,506,932

AME

$21,052,990

AME

$21,052,990

EDW

$17,325,477

EDW

$17,325,477

MERR

$16,351,848

MERR

$16,351,848

AMER

$14,120,564

AMER

$14,120,564

WELLS

$12,825,707

WELLS

$12,825,707

LINS

$8,709,050

COMPETITION - IBG

$12,237,331

UBS

$6,851,787

LINS

$8,709,050

E TRA

$6,784,443

UBS

$6,851,787

NEW

$5,281,292

Ohters

$71,750,223

Others

$59,618,668

$525,133,505

$490,600,082

Re: Table customization

Anonymous — Wed, 22 Nov 2017 12:58:45 GMT

Hi Van Your expression is working but its giving the Rank...I am using this expression in chart...I have a data table in which i have to populate those data...So how can i get my RTA$ value instead of Rank... Can u plz share the expression directly instead of sharing the work book... thanks for explaining in detail

Re: Table customization

avkeep01 — Wed, 22 Nov 2017 13:05:38 GMT

Hi Surya,

Expression used:

MIN(AGGR(IF(MATCH([Rollover Destination],'COMPETITION - IBG','UNKNOWN'), RANK(SUM([RTA $]),4)+15,RANK(SUM([RTA $]),4)),[Rollover Destination]))

You will need to have the rank column. Can't find any other way for that. I made a second expression:

Sum([RTA $])

Together they will form this chart:

Re: Table customization

Anonymous — Wed, 22 Nov 2017 13:17:20 GMT

My requirement is simple.... I just wanted to view top 15 contributors apart from COMPETITION - IBG & UNKNOWN.Those contributions has to be directly added to the 'Others' in default

Re: Table customization

avkeep01 — Wed, 22 Nov 2017 13:54:03 GMT

You could try this script in the script editor:

SourceTable:

LOAD * INLINE [

Rollover Destination, RTA $

FI, 176368433

CH, 49327118

VAN, 44383002

MOR, 30092771

UNKNOWN, 22230271

BAN, 21506932

AME, 21052990

EDW, 17325477

MERR, 16351848

AMER, 14120564

WELLS, 12825707

COMPETITION - IBG, 12237331

LINS, 8709050

UBS, 6851787

];

// Create rank based on RTA $, but if [Rollover Destination] equals COMPETITION - IBG or UNKNOWN then raise the rank.

temp_Table:

LOAD

[Rollover Destination],

IF(MATCH([Rollover Destination],'COMPETITION - IBG','UNKNOWN'),ROWNO()+15,ROWNO()) AS Rank,

[RTA $]

RESIDENT SourceTable

ORDER BY [RTA $] DESC;

DROP TABLE SourceTable;

// Sort by rank, name everything with rank higher then 15 others (just like in the front end) and group by the first 15 [Rollover Destinations], the rest will be shown as others.

DataTable:

NOCONCATENATE LOAD

IF(Rank >15,'Others',[Rollover Destination]) AS [Rollover Destination],

SUM([RTA $]) AS [RTA $]

RESIDENT temp_Table

GROUP BY IF(Rank >15,'Others',[Rollover Destination])

ORDER BY Rank;

DROP TABLE temp_Table;