topic Re: Tricky table view in QlikView

Tricky table view

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I have three columns, ID,analysis and results:

ID, Analysis, results:

A, x, 1

B, y, 2

C, z, 1

D, x, 3

E, z, 2

F, y, 3

Customer wishes to show these values as:

X Y Z

A 1 - -

B - 2 -

C - - 1

D 3 - -

E - - 2

F - 3 -

Is this possible to do in an easy way? I have several millions of IDs and hundreds of different analyses.

Many thanks!

Re: Tricky table view

m_woolf — Wed, 23 Nov 2016 13:43:13 GMT

Looks like a pivottable

Re: Tricky table view

adamdavi3s — Wed, 23 Nov 2016 13:47:19 GMT

and here is an example of how to do it

Re: Tricky table view

Anonymous — Wed, 23 Nov 2016 13:50:12 GMT

It is a pivot...Are you unsure how to achieve that pivot view or how to control how you will display that many records horizontally and vertically?

Re: Tricky table view

Anil_Babu_Samineni — Wed, 23 Nov 2016 13:55:37 GMT

Create Pivot table and then use

Dimension --- ID, Analysis

Expression --- Sum(results)

Then, Transpose Analysis from Column to Row level. I remind you this should done by pivot object only

Re: Tricky table view

Anonymous — Wed, 23 Nov 2016 14:19:08 GMT

The amount is tricky, it's actually to large for my document to load without crashing. My best choice so far is to let it stay as a tablebox and then do the pivot in excel for the by-the-customer-selected choices

Re: Tricky table view

Anonymous — Wed, 23 Nov 2016 14:31:42 GMT

Add a listbox with analysis records and put a textbox notifying the user they need to view a maximum of 10 (The number is up to you but lets say 10 for now) analysis at once OR use the straight table and pivot in excel - You will have to make both Straigt & pivot available to the end user. Either on seperate tabs or using Show hide based on a object to allow them to switch between the two

put this condition on the pivot chart

=getselectedcount(analysis)<=10 (if on seperat tabs)

vYourVariable = TheShowValueForPivot & getselectedcount(analysis)<=10 (if using show hide for the chart types)

This will only show a maximum analysis records of 10 or less and will be hidden when more are selected.

Re: Tricky table view

Anonymous — Wed, 23 Nov 2016 14:32:15 GMT

This should reduce\limit the load time and memory usage

Re: Tricky table view

sunny_talwar — Wed, 23 Nov 2016 14:32:56 GMT

If you want to do this in the script so that you can continue to use this as a table box, then may be this

Table:

LOAD * INLINE [

ID, Analysis, results

A, x, 1

B, y, 2

C, z, 1

D, x, 3

E, z, 2

F, y, 3

];

FinalTable:

LOAD DISTINCT ID

Resident Table;

FOR i = 1 to FieldValueCount('Analysis')

LET vField = FieldValue('Analysis', $(i));

Left Join (FinalTable)

LOAD ID,

results as [$(vField)]

Resident Table

Where Analysis = '$(vField)';

NEXT

DROP Table Table;

Re: Tricky table view

Anonymous — Wed, 23 Nov 2016 14:55:46 GMT

See attached. I used one of the approaches above to limit the number of analysis viewable.

I limited to 2 due to using sample data. You can change the variable to 10 or any number you wish.

Both pivot (Limited to X viewable analysis) and straight will be available.

Hopefull that helps...

Re: Tricky table view

Anonymous — Wed, 23 Nov 2016 15:13:47 GMT

Thank you for all your help!