Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

How to make Pivot table with Pick(Match compute faster for amount with -ve and +ve value ?

Hi All

Table 1 Expression :-

Money(Pick(

Match([Pls select 601st Dim],'sales','eXP','rEVENUE','nET_PROFIT'),

Sum (($(ColumnDim601)/$(Columndim89))/1000),

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89),

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89),

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89)),

$(vMoneyFormatK))

When I create the above pivot report. i notice that when my expression compute with positve value for example sales it have no issue. But when i compute eXP which have negative value it very slow , it there any way i can speed up ? As my issue is if i load the actual data , it will get out of memory error.

Table 2 Expression :-

Sum({<eXP={eXP}>}Amount/1000)/$(Columndim89)

For table 2 i have no issue .

Paul

1 Solution

Accepted Solutions

Re: How to make Pivot table with Pick(Match compute faster for amount with -ve and +ve value ?

Hi,

did you try your expression individually ,

like take on straight / pivot table

dimension same as in original chart

and expression

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89)


take [Pls select 601st Dim] in list box

select eXP in list box and how much time it will take to compute??

do same with other selection also check where issue is occurring.


Regards

Great dreamer's dreams never fulfilled, they are always transcended.
5 Replies
paulyeo11
Valued Contributor II

Re: How to make Pivot table with Pick(Match compute faster for amount with -ve and +ve value ?

my QV Doc

Re: How to make Pivot table with Pick(Match compute faster for amount with -ve and +ve value ?

Hi,

did you try your expression individually ,

like take on straight / pivot table

dimension same as in original chart

and expression

Sum({<$(ColumnDim601)={$(ColumnDim601)}>}Amount/1000)/$(Columndim89)


take [Pls select 601st Dim] in list box

select eXP in list box and how much time it will take to compute??

do same with other selection also check where issue is occurring.


Regards

Great dreamer's dreams never fulfilled, they are always transcended.
paulyeo11
Valued Contributor II

Re: How to make Pivot table with Pick(Match compute faster for amount with -ve and +ve value ?

Hi Sir

I not yet try your proposal . I will try it out soon.

I have been using match(pick for last 6 month for all my chart and table with many type of expression , I encounter issue when field = eXP.

So I know the issue is when value with +ve and -ve have issue.

Paul

Sent from my iPhone

paulyeo11
Valued Contributor II

Re: How to make Pivot table with Pick(Match compute faster for amount with -ve and +ve value ?

Hi Max

I have try your approach , so i create below in line load script. ( See the last Tab )

LOAD * INLINE [

variable,field

eXP,eXP

nET_PROFIT,nET_PROFIT

rEVENUE,rEVENUE

];

I have forgotten what should be the next step , as too long never use this. can you guide me thru a bit ?

My question is how to display the field ? So that i can select eXP or rEVENUE

Paul

paulyeo11
Valued Contributor II

Re: How to make Pivot table with Pick(Match compute faster for amount with -ve and +ve value ?

Hi MAx

I manage to figure out . and i found that your approach working fine.

it speed up a lot. i will use your approach.

Paul

Community Browser