Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Calculation in Data Load Editor

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

Tags (3)
6 Replies

Re: Calculation in Data Load Editor

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

arvind_patil
Valued Contributor II

Re: Calculation in Data Load Editor

Hi Alison,

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

Thanks,

Arvind Patil

tinaco110
Contributor III

Re: Calculation in Data Load Editor

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;

Not applicable

Re: Calculation in Data Load Editor

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

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


tomasz_tru
Valued Contributor

Re: Calculation in Data Load Editor

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