topic Re: Create the caliculate Dimension in QlikView

Create the caliculate Dimension

ravindraa — Wed, 03 Sep 2014 04:31:26 GMT

Hi all,

I have struk one place Plase give any suggession to me to resolve my problem

My requirment is we have data existed like below in data base.

Name ModelType value

OldRenolds OLD_R 10

OldRenolds_PP OLD_R_PP 15

NewRenolds NEW_R 20

Balpen Balpen 50

***OldRenolds->means OLD_R + OLD_R_PP

****Renolds -> means OLD_R + OLD_R_PP+NEW_R

In out put need to diplay like below.

ModelType Value

OldRenolds 10

NewRenolds 20

Balpen 50

Renolds 45

to get above out put i have tried like below

If(ModelType='NEW_R','NewRenolds',

if(ModelType='Balpen','Balpen',

if(ModelType='OLD_R' or 'OLD_R_PP' ,'OldRenolds',

if(ModelType='OLD_R' or 'OLD_R_PP' or 'NEW_R','Renolds')))) as ModelType_New

In the ModelYype_New dimension i am not getting the *Renolds* Fiels and its value

i am getting all fields and thier values but rest of Renolds Field and its value.

Please give me any suggession to get this.

Re: Create the caliculate Dimension

PrashantSangle — Wed, 03 Sep 2014 04:39:43 GMT

Hi,

What is your expression to calculate Value?

What issue you are getting with this calculated dimension experession?

Your calculated dimension experession looks fine for me just use sum(Value) as expression.

Regards,

Re: Create the caliculate Dimension

ravindraa — Wed, 03 Sep 2014 04:50:13 GMT

Hi MaxDreamer,

Thanks for Replying but i am not getting the Renolds Fields and its value, i have modified my erliaer post please find that please give me any suggesion on its.

Re: Create the caliculate Dimension

PrashantSangle — Wed, 03 Sep 2014 04:55:00 GMT

Hi,

In If clause

You have to write

If(ModelType='NEW_R','NewRenolds',

if(ModelType='Balpen','Balpen',

if(ModelType='OLD_R' or ModelType='OLD_R_PP' ,'OldRenolds',

if(ModelType='OLD_R' or ModelType='OLD_R_PP' or ModelType='NEW_R','Renolds')))) as ModelType_New

Or you can try with wildmatch() or match() also

If(wildmatch(ModelType,'NEW_R'),'NewRenolds',

if(wildmatch(ModelType,'Balpen'),'Balpen',

if(wildmatch(ModelType,'OLD_R','OLD_R_PP' ),'OldRenolds',

if(wildmatch(ModelType,'OLD_R','OLD_R_PP','NEW_R'),'Renolds')))) as ModelType_New

Regards

Re: Create the caliculate Dimension

ravindraa — Wed, 03 Sep 2014 05:09:04 GMT

Hi Max dreamer,

thanks for replying still i am not getting the Renolds Fields and its value.

if ther is any modification do i need to do please give me suggesion .

Thanks .

Re: Create the caliculate Dimension

PrashantSangle — Wed, 03 Sep 2014 05:15:41 GMT

Hi,

can you share sample apps?

Regards

Re: Create the caliculate Dimension

preminqlik — Wed, 03 Sep 2014 05:19:45 GMT

Hi there, try this

dimension :

=ValueList('OldRenolds',

'NewRenolds',

'Balpen',

'Renolds')

expression :

pick(match(ValueList('OldRenolds',

'NewRenolds',

'Balpen',

'Renolds')

,'OldRenolds',

'NewRenolds',

'Balpen',

'Renolds'),sum({<ModelType={'OLD_R','OLD_R_PP'}>}value),sum({<ModelType={'NEW_R'}>}value),sum({<ModelType={'Balpen'}>}value),sum({<ModelType={'OLD_R','OLD_R_PP','NEW_R'}>}value))

Re: Re: Create the caliculate Dimension

tresB — Wed, 03 Sep 2014 05:29:46 GMT

With nested if this won't work because for a certain row/record it can't be true for more than once. I would try creating mapping/association table like:

Input:
Load * Inline [
Name,ModelType,Value

OldRenolds,OLD_R,10

OldRenolds_PP,OLD_R_PP,15

NewRenolds,NEW_R,20

Balpen,Balpen,50
];

Map:
Load * Inline [
ModelType, NewModelType
OLD_R, OldRenolds
OLD_R_PP, OldRenolds
OLD_R, Renolds
OLD_R_PP, Renolds
NEW_R, Renolds
Balpen, Balpen
NEW_R, NewRenolds

];

PFA.

Or, if you want in the front-end, you have to use synthetic dimension using valuelist().

Re: Create the caliculate Dimension

Wed, 03 Sep 2014 05:44:59 GMT

Hi Ravindra,

Please find the attached QVW.

Let me know if anything required.

Thanks,

Chiru

Re: Re: Create the caliculate Dimension

ravindraa — Wed, 03 Sep 2014 05:56:10 GMT

Hi maxDreamer,

please find the below atachment.

Re: Re: Create the caliculate Dimension

ravindraa — Wed, 03 Sep 2014 06:46:59 GMT

Hi Tresesco,

Thanks you very much for giving the help.