Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Alison,

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

Thanks,

Arvind Patil

ArnadoSandoval
Specialist II
Specialist II

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;

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Not applicable
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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


Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
tomasz_tru
Specialist
Specialist

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