4 Replies Latest reply: May 6, 2010 5:36 AM by koertklomp RSS

    Calculated fields

      In our backoffice system for price calculation we work with calculated fields, so the values are not stored in the database. What is the best way to handle these values in QlikView? Is it possible to use/call the functions (Progress software and DB) in QlikView? Or... Please advise.

      Kind regards,

      Koert

        • Calculated fields
          Julian Villafuerte

          Hello,

          Maybe the best way for you to work with those fields is to create them in your script. You can use some useful QlikView functions there, so I don't think you'll have any problem. That way, when you start creating your reports and charts, the information will be available.

          Hope it helps!

            • Calculated fields
              juergm

              Suggest you keep your business rules in one place - you have to know by yourself where this is. If you use functions to calculate the values just use these in the SELECT from your data.

              If you recreate the formulas in QlikView you might end up in differences between your existing reports and the QlikView results.

                • Calculated fields
                  John Witherspoon

                  I agree with keeping business rules in the business system, at least if there's any complexity to them. If your business rules are as simple as "Profit = Revenue - Cost", that's pretty simple and universal, so I see no problem with sticking it in QlikView if it simplifies things.

                  But I agree with you that you'd generally want to use/call the functions (Progress software and DB). I don't know anything about Progress software, and I don't know what DB you're using and what you mean by a function in the DB, so I can't answer explicitly how to do it.

                  I can give an example of applying the general philosophy, though. We have a business system where there is a layer of data which are basic facts about things that happen. On top of this sits a bunch of business rules, which essentially transform the data layout from the database into a very different layout that is actually used by the business system. Programs do not read the database directly. They use the routine that executes the business rules to transform the raw data. This is all done on the fly in real time with no problems.

                  Unfortunately, that makes things inconvenient for QlikView. It could read the database directly, but then we would have to duplicate all of the business rules in QlikView. Not acceptable. But due to technical issues, we also can't make it call the routine that retrieves and transforms the data for the business system.

                  Our solution was to schedule a daily program in the business system to extract and transform the data according to the business system rules, using the common routine, and then dump the data to a flat file. QlikView then reads in the flat file.

                  I don't like using flat files as a means of communication between systems, but in this case, it was the best I could come up with.