Cross join and modelling issues

[#1]

Hello,

I made an application whose purpose is to do assessment and cost simulations.

My measures are decomposed by basics elements which can be substituted with user parameters.

I would like being able to answer "what happen on global computation if I increase by 30% the parameters x and y" or "if I increase all parameters by 50%, which ones would have the biggest impact on the computation.

I created a table (called SimulationValue in my example) which contains all parameters with all potential percentage value I want.

I don't know how to integrate this table in my model. When, I select only one value in this table, my computation are fine.

But since my "SimulationValue" table is not linked to my model, multiple parameters selection will cause cross join and computation will be wrong.

I'm looking for a way to include this table correctly in my model or, a way to change my formulas to ignore irrelevant values generated by the cross join.

In my example, I have highlighted in blue three objects to show what I'm trying to do.

Do you think it's possible to achieve what I'm trying to do ?

Thank you in advance for any hints,

Jean

[Gysbert_Wassenaar]

I think I'd generate variables for all the parameters and use input boxes or sliders to select the value for the simulation. You could also use variables for the upper and lower bounds of the ranges of valid values for the parameters.

[#3]

Hi Gysbert,

I had already tried your solution (an example of it is on the 2nd tab of the app).

But then, I wanted to improve the solution so I could do charts or table showing all potential values on y-axis. For this reason, I have introduced the "values" table.

Another reason was also to avoid qlikview app update when new parameters were introduced into the database.

[Gysbert_Wassenaar]

Well, my idea was not to create the variables manually, but generate them from a source table. That way new parameters will automatically result in new variables.

If you want to show all potential values of a parameter in a chart then I don't think one table will work. I think you'll need one table for each parameter.

[#5]

Hi Gysbert,

Thanks for your answer. I didn't got the time to answer you yesterday.

Concerning you solution, I'm not sure to understand how variables can be generated from a source table. Is it something that I can do in my loading script ?

To show all potental values of a parameter in a chart, if I create one table by parameters I will have to edit my model each time a new parameters appears. Right ? In this case, my charts will have to be "statics" (no way to let the users change the parameter they want to analyse

Regards,

Jean

[Gysbert_Wassenaar]

The tables too can be generated from the source table. Below an example that generates variables and tables.

//loop through the parameter names from ParamStudied
for i = 1 to FieldValueCount('ParamStudied')

  //generate variables example
  LET vP = purgechar(replace(FieldValue('ParamStudied', $(i)),' ','_'),'()');
  LET v$(vP) = 0; //initial value

  //generate tables example
  $(vP):
  NoConcatenate
  LOAD PotentialValue as [$(vP)_potential_value]
  RESIDENT SimulationValue
  WHERE ParamStudied = FieldValue('ParamStudied', $(i));

next

[#7]

Thanks a lot for this example Gysbert! I didn't know tables could be generated like this.

I will try this solution this week and will keep you informed if it worked for my problem.

Regards,

Jean