6 Replies Latest reply: Jul 6, 2017 6:00 AM by Tomasz Truszkowski RSS

    Calculation in Data Load Editor

    Alison Cooke

      Hello All,

       

      I would like to preform the following calculation: Income / Expenditure.

      However, the fields income and expenditure are found in different tables. As so:

       

      Screenshot (25).pngScreenshot (26).png

       

      How should I preform this calculation. Someone mentioned that I need to preform a join between the income and expenditure tables. Is that so?

       

      Thank you all in advance,

      Alison

        • Re: Calculation in Data Load Editor
          Kaushik Solanki

          Hi Alison,

           

          There is no point in creating multiple post for the same questions.

           

          Now I can see that your income and expense table has date common between two.

           

          So you can perform join like below.

           

          Data:

          Load Date,Income from Income;

           

          join

           

          Load Date,Expenditure from Expenditure ;

           

           

          Final:

          Load Date,Income/Expenditure as Result resident Data;


          Drop table Data;


          Hope this is clear to you.


          Regards,

          Kaushik Solanki

            • Re: Calculation in Data Load Editor
              Alison Cooke

              Hi Kaushik,

               

              Apologises for doing so but I thought that I didn't express myself very well in the first instance. I would have liked to have deleted the first post but I don't have the ability to do so.

               

              When I enter the script and load the data I get the following error:

              The following error occurs. Why is this the case?

              No qualified path for file: ***

              The error occurred here:

              Data: Load Date,Income from Income

                • Re: Calculation in Data Load Editor
                  Kaushik Solanki

                  Hi,

                   

                  You need to change the table name for Income to FinalIncome from FinalExpenditure.

                   

                  Once you do this your script should look like below.

                   

                  Data:

                  Load Date,Income from FinalIncome;

                   

                  join

                   

                  Load Date,Expenditure from FinalExpenditure ;

                   

                   

                  Final:

                  Load Date,Income/Expenditure as Result resident Data;


                  Drop table Data;


                  Drop tables FinalIncome,FinalExpenditure;


                  Regards,

                  Kaushik Solanki


              • Re: Calculation in Data Load Editor
                Arvind Patil

                Hi Alison,

                 

                You need to combine those fields in a table. and then you need to perform the calculation.

                 

                Thanks,

                Arvind Patil

                • Re: Calculation in Data Load Editor
                  Arnaldo Sandoval

                  Hi Allison,

                   

                  You may not need to join tables, that's a database thinking approach, by looking at your screenshots, you ended with a FinalExpenditure table with 3 columns; I am listing them below (it may not be exactly what Qlik does, it is just to give you an idea).

                   

                  Final Expenditure

                  1. Date
                  2. [Expenditure Amount]
                  3. [Income]

                   

                  Depending on the Date formatting in use, you could have a single row per day per Income row, and a single "Expenditure Amount" per day per expenditure, by example:

                   

                  Date "Expenditure Amount" Income.

                  01/07/2017; 500; null or zero

                  01/07/2017; null or zero; 1000

                  02/07/2017; 750; null or zero

                  02/07/2017; null or zero; 500

                  ...

                  and so on ...

                  ...

                  Your table FinalExpenditure should look pretty close to the one above, now what you need to do in your script is something like this.

                   

                  NoConcatenate;

                  Result:

                  Load

                             *,

                             iif( [Expenditure Amount] = 0, 0, [Income] / [Expenditure Amount] ) as Result;

                   

                  Load

                            Date,

                            Sum( [Expenditure Amount] ) as [Expenditure Amount],

                            Sum( [Incomen] ) as [Income] as [Income]

                  Resident FinalExpenditure

                  Group by Date;

                  • Re: Calculation in Data Load Editor
                    Tomasz Truszkowski

                    If you really don't want to join those tables you can try to use ApplyMap: LivingQlik Roots: The Complete Guide to QlikView Mapping Load (use case 4).