Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ravindraa
Creator
Creator

Create the caliculate Dimension

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.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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().

View solution in original post

10 Replies
PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
ravindraa
Creator
Creator
Author


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.

PrashantSangle

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

Like

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
ravindraa
Creator
Creator
Author

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 .

PrashantSangle

Hi,

can you share sample apps?

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
preminqlik
Specialist II
Specialist II

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))

tresesco
MVP
MVP

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().

Not applicable

Hi Ravindra,

Please find the attached QVW.

Let me know if anything required.

Thanks,

Chiru

ravindraa
Creator
Creator
Author

Hi maxDreamer,

please find the below atachment.