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

Matching two different tables in one straight table.

Hi,

I'm trying to reconcile general ledger data with trial balance. General ledger contains all of the account transactions and trial balance contains all of the account balances.

I've tried to perform reconciliation in following way:

1. I have summarized account balaces in general ledger by using straight table. This gives me unique account numbers and one balance for each account. Example:

Orginal general ledger
New straight table (summarized general ledger)
Account Amount
AccountAmount

1111

100SUMMARIZE ->1111150
1111-50
1111100
22221002222300
2222200
333330033333300
etc.etc.etc...

etc..

2. Next, I'm trying to bring trial balance accounts and balances next to summarized balances for comparison. Example:

Straight table (summarized general ledgerTrial balance
AccountAmount
AccountAmountDifference
1111150111120050
222230022223000
333330033333000
etc.etc...etc...etc...etc...

Problem is that I can't figure out how to match accounts in G/L and trial balance. I was able to bring them into same straight table but they did not match. Moreover, when I brought trial balance account to straight table with summarized G/L accounts, the table didn't summarize them correctly anymore but instead listed all of the individual account transactions.

What am I doing wrong and is this the best way to accomplish this type of task? Could some one point me into right direction?

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Without being able to see your model or a sample, I can only guess at the problem - here's my guess.

I suspect that you have a different field name for you GL account number in the two source files and you have loaded those into two separate tables. Load the data something like this:

Data:

LOAD GLAccount,

     Amount,

     'Trans' As Source ,

     ....

FROM GLTransacations;

Concatenate

LOAD [TB GL Account] As GLAccount,

     [TB Amount],

     'TB' As Source,

     ...

FROM TrialBalance;

Now create a straight table with GLAccount as the dimension, Sum(Amount) as the GL amount, Sum([TB Amount]) as the TB amount and COlumn(2) - Column(1) as the difference.

If you need more specific help, I suggest that your post your qvw or a at least a sample. If the data is confidential, scramble it before uploading.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Without being able to see your model or a sample, I can only guess at the problem - here's my guess.

I suspect that you have a different field name for you GL account number in the two source files and you have loaded those into two separate tables. Load the data something like this:

Data:

LOAD GLAccount,

     Amount,

     'Trans' As Source ,

     ....

FROM GLTransacations;

Concatenate

LOAD [TB GL Account] As GLAccount,

     [TB Amount],

     'TB' As Source,

     ...

FROM TrialBalance;

Now create a straight table with GLAccount as the dimension, Sum(Amount) as the GL amount, Sum([TB Amount]) as the TB amount and COlumn(2) - Column(1) as the difference.

If you need more specific help, I suggest that your post your qvw or a at least a sample. If the data is confidential, scramble it before uploading.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sujeetsingh
Master III
Master III

Hi Rao,

Actually this depends on your data model so please explain about it.

Not applicable
Author

Hi,

Thanks for both of you! I had to relate G/L data and trial balance by account number. After that I could sum the G/L accounts and match them with TB accounts.

Not applicable
Author

Hi,

I have a little continuation question on the topic.

I was able to reconcile G/L with Trial Balance. However, there are a couple accounts in G/L that are not in TB and a couple accounts in TB that are not in G/L.

In my reconciliation table there are only accounts that are found in both G/L and TB. Is it possible to create two fields in reconciliation table: G/L account and TB account. If either is missing from the other table, values in that row would be show up as blanks. Something like this:

G/L Account
TB account
G/L account balance
TB account balance
Difference
100010001001000
200020002002000
3000-400-400
-4000-100-100
500050001501500

Here's a screenshot of my data model if it helps:

datamodel.PNG