Selective Selection on Pivot Table .

[#1]

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.

[#2]

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.

[#3]

Hi Mark ,

Thanks Again .

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

Regards

Debashish.

[#4]

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.

[#5]

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

[#6]

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