Math on two different sets of data

[#1]

Hi everyone,

I'm currently working with data for a department within my bank. This data is stored every month in qvd files. I have a field that contains a date, and several other fields that join to that date for each loan. I am interested in looking at a the current balance field for the most recent month end date, and subtracting the current balance from the previous month end from that to see the difference. Only problem is that I am having an issue doing this because I am not sure how to work with two different sets of data. Is there a way to do this? Also, I'd like to work at the document level rather than the data model level if that is possible.

Thank you.

[jerem1234]

Would you be able to put together an example of what you are trying to do?? Some mock data and then what the output should be?

Even better would be a sample qvw demonstrating an example of what you have and then what you want to achieve.

[JonnyPoole]

It sounds like you need to concatenate the different source files together.  (concatenate is like a UNION or append)

Name the date fields in each table the same so that they become one field with values from all files. here is an example:

Table1:

date,

....

from <source1>;

concatenate(Table1):

Otherdate as date,

....

from <source2>;

Once the tables are concatenated together, you then need a technique to quickly grab current month and last month.

For that i would suggest building a calendar table in the data model that has date, year, month etc... and add a few calculated boolean flag fields like current month and prior month. the values of the flag fields are 1 or 0 depending if the date record is within the current month or prior month or not. Once you have the flag, the expressions are straight forward:

sum(  {$< CurrentMonthFlag={1}>}  LoanAmount}

sum(  {$< PriorMonthFlag={1}>}  LoanAmount}

2 parts to the solution and both have a few extra details but post any followups you might have.