topic Selective Selection on Pivot Table . in QlikView

Selective Selection on Pivot Table .

Tue, 28 Apr 2009 22:55:40 GMT

I have been working with Qlikview for last 1 month. I am working in a 64 bit Environment with Windows 2003 EP Edition , with 32 GB of RAM. We have 1 Billion records. that i am working with.

The requiremnt is a Pivot Table.

I was able to develop the Pivot Table. But i am stuck in one selection.

I am uploading the QVW file , which will give you the idea of the Problem.

If i want to see al the Values of the Parameters where X=12

Then it deselects other Parameters like L,M,N,Y . As i have selected 12 , which inturn selects X.

I did try to use the Loosen Table functionality. But i was unsussful.

Can some body give me an idea , how to tackle this kind of Problem.

Selective Selection on Pivot Table .

Tue, 28 Apr 2009 23:14:24 GMT

I think a table box (in stead of a pivot table) will do the what you are after. See attached for amends to your file that shows what I mean.

Regards,

Selective Selection on Pivot Table .

Tue, 28 Apr 2009 23:27:18 GMT

I would need a pivot table only as , if we select the event = 2 the we get diffrent set of coulmns. ( A,B,C) and if we select event-=1 then we get columns like L,M,N,X,Y

I requiremnet is that if we select Event=1 , the we get the Pivoted columns like L,M,N,X,Y .

But if we further select Evalue as 12 then i see only one column that is X.

But i wanted to see all the columns from L,M,N,X,Y where X=12.

Selective Selection on Pivot Table .

Wed, 29 Apr 2009 00:04:57 GMT

I wanted to perform a selection after the Pivot table is generated , as i can use the filter on the newly generated columns.

Selective Selection on Pivot Table .

Wed, 29 Apr 2009 00:41:28 GMT

I've created an extra table of EP_ID and a merge of parameter and value. Using this merged field as select box van possibly solve your problem. Give it a try.

Selective Selection on Pivot Table .

Wed, 29 Apr 2009 03:58:47 GMT

Hi Mark ,

Thanks for the Solution . Sorry for not able to give you a better example.

Actually the field that is being displayed in the Pivot table is not exactly Value , but a nested if block on Evalue / Value based on the Event & parameter Column.

But another hitch is :

My table record is 740 MM , so when i try to do another Load , it load again 740 MM times.

"LOAD EP_ID, Parameter&' '&Value AS Selection RESIDENT Event;"

Selective Selection on Pivot Table .

Wed, 29 Apr 2009 22:14:58 GMT

This Load Again Takes a lot of time : Do you have any suugestions to over come this.

Selective Selection on Pivot Table .

Thu, 30 Apr 2009 09:49:35 GMT

Hi Mark ,

The below expression,

LOAD EP_ID, Parameter&' '&Value AS Selection RESIDENT Event

works well for small number of data . But the no of records in my case is 740 MM .

When i try to use it , it fails afetr 490 MM records. (taking 6 Hrs).

Its gives the Error , Fails to load the Application.

I tried to relod the application 3 time , but every time after 400 MM to 500MM record processing it fails to Concatenate further rows.

Regards

Debashish.

Selective Selection on Pivot Table .

Fri, 01 May 2009 03:25:42 GMT

Maybe a simple macro can be a solution for your problem, I added a button to your QlikView document to give you an example. Good luck!

Mark

Selective Selection on Pivot Table .

Fri, 01 May 2009 21:44:39 GMT

Mark Thanks , this Macro really solved the Problem. I do not have to go throgh the costly concatenation operation.

But i also have a small query , can i somehow do an And Operation on my selection

Like X=12 and Y<4 ,

Can multiple And condition be possible in Main Page.

Regards

Debashish.

Selective Selection on Pivot Table .

Fri, 01 May 2009 21:57:10 GMT

You can use the macro twice:

-Select parameter X
-Select Value 12
-Press Filter button
-Select parameter Y
-Search Value box: select Value box and press F3 and enter <4, and press ENTER
-Press Filter button again.

Selective Selection on Pivot Table .

Fri, 01 May 2009 22:14:43 GMT

Hi Mark ,

Thanks Again .

Can i some how know what are the filter conditions that i have applied . One after Another.

Regards

Debashish.

Selective Selection on Pivot Table .

Fri, 01 May 2009 22:23:31 GMT

You could store them in a variable, comma separated, after button press. But then you should use another button to clear the selection and this variabele.

Selective Selection on Pivot Table .

Fri, 01 May 2009 22:57:21 GMT

Hi Debashish,

A couple of points:

1. My strong recommendation would be not to use macros unless you absolutely have to. Using macros to perform data operations have a tendency to affect (clear) your caches, hence affecting end user performance over time. If you need to perform concatenation operations to pre-process, combine or otherwise manipulate field contents at a row by row level, I suggest doing so at the earliest point of loading the data rather than through second pass operations like RESIDENT loads - typically in LOAD statement directly preceding your SQL statements pulling the data from the database.

* You may already be doing this, but just in case you haven't explored this yet: For a billion row application I would recommend looking into Change Data Capture techniques to only pull back what you haven't seen before. Is this what you are currently doing? Old data can be stored in the QlikView native format .QVD for fast access whilst new data would have to be pulled from the database. On a regular interval you can thus update your historical .QVD files to over time build a fast-access history of your data.

2. To know what filters you've applied, you have the 'Current Selections Box' object. If you need the actual selections as a string for expression building purposes, use either the GetFieldSelections(<fieldName>) function or GetCurrentSelections().

Regards,

Jonas

Selective Selection on Pivot Table .

Tue, 12 May 2009 09:40:43 GMT

Hi Jonas,

Thanks for your Tips. I am using the Change Data Capture to only pull data that we need.But i do not think i can avoid Macros.

Let me explain you what i am trying to achive in Qlikview.

We have a Time stamp Column called "Time"
We have diffrent machines (Calling it JNo)
Machines while running has diffrent events (calling Events , eg "Environmnet")
Each Events has many Event ID (Calling LEID eg 5,6,7,8,9 )
Each Event has fixed Number of Parameters like for "Environment" we have 3 parameters "Board or Device" , "Result" , "Device".
These Parameters have one Value (calling Pvalue)
Parameters may have fixed Value or may not have .

Like the parameter "Roator" has only 2 Values Start=1,Stop=0.
Some parameters do not have fixed value. like "SD Limit" which can assume any value.

The Fixed parameters and there values are in the ENUM & Evalue Table.

------------------------------------------------------------------------------------

Pivot table that i want to generate is :

we want to see all the Events which has the Roator Value as Start (Pdesc=Roator , Pvalue=1)

-----------------------------------------------------------------------------------

Solution: 1.

I Wrote the Macro to do this operation.
Its working fine with small number of records.
I tried to use the Macro for 740MM records in my 64bit m/c , (Windows 2003 EP Edition with 32 GB of RAM).
The Pivot table which i am trying to achive becomes very slow , and at times goes out of memory.

--------------------------------------------------------------------------------------

Solution: 2.

I created another Table by concatenating the PID , pvalue .

and using it as the basis of selection of "LEID"
to generate the Pivot table.

This Solution is not user friendly ( user do not need to know the Pid value and Multiple filters is not possible ) and also slow like the above one.

Attached is the QVW file that will give you the clear picture.

Can you please suggest how can i achive the pivot selection faster / user Friendly avoiding Macros.

Regards

Debashish