topic Re: Changing of dimension values in QlikView

Changing of dimension values

jacqueshol — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have probably been abusing the good support of the Qlik community the past few days, but have been assisted wonderfully throughout. So, after endlessly searching for an answer, I thought I'd try the community again for some answers. Once again, I'm pretty sure that a simple solution exists, but as a newbie, it escapes me...

I have a number of CostTypes (>100), e.g. Salaries, Wages, Rent, Overtime, etc. Each of these are classified as either a FIXED or VARIABLE cost. I have set these in my input datasheet (Excel). E.g.

CostType	Classification
Salaries	Fixed
Wages	Fixed
Rent	Fixed
Overtime	Variable

However, there has been some debate among ourselves about the classification of some of these costs and I want to be able to change these classifications in QlikView, rather than in Excel and reloading the data every time. Is this possible? E.g. Wages has initially been categorised as a FIXED cost, but if we decide that it should be a VARIABLE cost, I'd like to make this change in the model so that it subsequently updates all my calculations based on FIXED and VARIABLE. Am I making sense?

I've tried to use InputField, but my lack of knowledge has prevented me of coming up with a workable solution.

Any assistance would be greatly appreciated.

Take care,

Jacques

Re: Changing of dimension values

kaushiknsolanki — Mon, 01 Aug 2016 09:24:49 GMT

Hi,

There are two things to this.

If you have done any calculation based on the classification in Script then the calculation will not change if you change the classification from InputField.

second if you do the logic calculation on front end, it may happen that your application start giving performance issue, because everytime value will change, all expressions will have to be recalculated and that too on the fly.

So what I would suggest is you move your calculations to script and then you use the mapping functions to change the current classifications, so that your calculation will change depending on the mapped value of the field.

Regards,

Kaushik Solanki

Re: Changing of dimension values

Mon, 01 Aug 2016 09:34:15 GMT

you should go with excel and define calculation in script depending on classification in excel file. whenever you changed something in excel, you calculation automatically will change depending on classification value.

Re: Changing of dimension values

jacqueshol — Tue, 02 Aug 2016 05:40:44 GMT

Hi, thanks to all for answers, but I am really not understanding. Firstly, there are no calculations based on FIXED or VARIABLE in script. We are all around a boardroom table, interrogating the costs. I have a basic table chart summing the FIXED and VARIABLE costs. I simply want to be able to change a CostType from, say, FIXED to VARIABLE and then the sum of these need to recalculate. From what I gather from the responses a simple solution is not forthcoming.

Re: Changing of dimension values

kaushiknsolanki — Tue, 02 Aug 2016 08:26:22 GMT

Please share the sample data with logic and expected output.

Regards,

Kaushik Solanki

Re: Changing of dimension values

Anonymous — Tue, 02 Aug 2016 08:32:17 GMT

Maybe you could create a mapping table in your data model to map each cost type to a classification then all you would need to change is the classifications in the mapping table and reload. Not sure if this meets your requirements. Maybe you could share some more details about your requirements?

Re: Changing of dimension values

jonathandienst — Tue, 02 Aug 2016 08:37:38 GMT

There is no simple solution to do what you want. It may be possible to cobble something together using variables, bookmarks and possibly alternate states, but it is not that simple.

Re: Changing of dimension values

jacqueshol — Tue, 02 Aug 2016 09:26:14 GMT

Thanks Jonathan. Back to Excel it is...

Re: Changing of dimension values

jacqueshol — Tue, 02 Aug 2016 09:29:13 GMT

The file is rather large with lots of confidential data, so I'll upload a shortened version shortly

Re: Changing of dimension values

Mark_Little — Tue, 02 Aug 2016 09:36:41 GMT

Hi,

Like people have suggested it is probably as easy to do it in excel, unless there is set logic to changing this. if so you can do in script dynamically

something like

IF(Field > 100, 'Fixed', Classification) as Classification

otherwise if you are just choosing some to be changed then the values need to be kept and updated some where.

Mark

Re: Changing of dimension values

jacqueshol — Tue, 02 Aug 2016 09:57:58 GMT

Hi Kaushik,

Thank you for your willingness to assist. Below I have inserted an image with a very simple database. You will notice that in the Detail Table, Rent is listed as a FIXED cost. What I want to be able to do, for example, is to change that cost to VARIABLE.

Here is the amended datatable with the resultant changes in the Summary Table and Pie Chart. As you will notice the % splits has changed. I did this in the source file (Excel).

I'd like to know if there is a way to do this in QlikView (by changing the Classification in my Detail Table), without having to go back to source. We have a model that is reading millions lines of data and it is just not feasible to change it in source and reload it every time. As mentioned before, I have tried my way around the InputField option, and even variables, but just couldn't get it to work.

Thank you again kindly for assisting me.

Take care,

Jacques

Re: Changing of dimension values

jacqueshol — Tue, 02 Aug 2016 10:12:34 GMT

Thanks Mark,

Unfortunately it isn't a calculated value (based on a value). It is a subjective decision that is made on the fly by our management team as well as our customer's. They review each cost and then debate whether it is a Fixed or Variable cost. Most costs are easily determinable, but as you can imagine, that when you have 8 chartered accountants in a rooom, you are going to get at least 15 different opinions . That is why I want to be able to have a list of costs and perhaps a sort of dropdown box to select whether the cost is Fixed or Variable. As the discussions continue, the guys may change their mind about a previous cost and I want to be able to change it quickly without having to reload which takes minutes each time.

Haha, it seems I stumped you all. My boss is already telling me that this proves that Excel is a better tool.... *sigh*. Really frustrating to see the potential of a tool, but not able to use it to its fullest...