Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Aggregating data from specific clients in associated databases

Dear all,

First, apologies if this has been asked before. I am attaching a file that has 3 sheets that explain my problem. I have three databases, and I need to aggregate data for specific clients using data from 2 databases (assets in one, and liabilities in another). Using the client database (or sheet in the Excel) I want to look for specific client ID in the other 2 databases and add their respective amount of assets and liabilities. Not all clients have assets data and not all have liability data as you can see.

How can I complete column D in the Client Sheet? Do I need to code?

Thank you very much for your help.

Best regards,

Alvaro

4 Replies
Not applicable

Re: Aggregating data from specific clients in associated databases

Not sure if the Excel was attached before. Here it is. Thanks!!

ahaahaaha
Honored Contributor

Re: Aggregating data from specific clients in associated databases

Hi Alvaro,


May be as at attached file.


Regards,

Andrey

Not applicable

Re: Aggregating data from specific clients in associated databases

HI Andrey,

First of all, apologies for such a long delay in my response. You took the time to try to solve this and I didn't get back to you. I went on leave all July and then I missed to respond.

This looks exactly to what I wanted to do. I will try to use the same commands you used in the data load editor in the sheets I have with the real data. I might bother you if i have any question.

Thank you!

Alvaro

Not applicable

Re: Aggregating data from specific clients in associated databases

Hello again Andrey (or anyone who might be able to help),

The table you created with column "Assets+Liability" by Client ID is the data I wanted to get. However, I want to manipulate that data or use it as a possible dimension in order to sort, group by specific amounts, or do other calculations using it. This is not possible as far as I know if the data is in a table instead of as a new variable. What I would like is to have a the new variable "AUM" in the Client2 dataset filled with that data and I believe the coding is different in that case. I am relatively new to coding in the data load editor but I wasn't able to create a new variable or fill the "AUM" variable in the "Client2" Database.

If you or anyone can help me finally solve this it would be much appreciated. I am attaching the file "Qlik-Challenge.qvf" file which has 3 datasets: Client2, Assets, and Liability. What I would like to do is the same as you did with the table but including the content of "Assets+Liability" in the "Client2" database (in the AUM column which currently has "0" in it). As you will see it is not just adding "Assets+Liability" because what I actually need is to add those two variables from the other datasets but for specific "client_IDAT" or client IDs. I believe this is what you did with the command "Left Join".

Anyway, thank you all for your awesome help.

Best regards,

Alvaro