topic Re: Matching two different tables in one straight table. in QlikView

Matching two different tables in one straight table.

Tue, 26 Feb 2013 08:47:39 GMT

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:

Account	Amount		Account	Amount
Orginal general ledger			New straight table (summarized general ledger)
1111	100	SUMMARIZE ->	1111	150
1111	-50
1111	100
2222	100		2222	300
2222	200		2222	300
3333	300		33333	300
etc.	etc.		etc...	etc..

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

Account	Amount	Account	Amount	Difference
Straight table (summarized general ledger		Trial balance
1111	150	1111	200	50
2222	300	2222	300	0
3333	300	3333	300	0
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?

Re: Matching two different tables in one straight table.

jonathandienst — Tue, 26 Feb 2013 09:26:07 GMT

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

Re: Matching two different tables in one straight table.

sujeetsingh — Tue, 26 Feb 2013 09:51:41 GMT

Hi Rao,

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

Re: Matching two different tables in one straight table.

Tue, 26 Feb 2013 13:11:10 GMT

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.

Re: Matching two different tables in one straight table.

Wed, 27 Feb 2013 07:36:54 GMT

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
1000	1000	100	100	0
2000	2000	200	200	0
3000	-	400	-	400
-	4000	-	100	-100
5000	5000	150	150	0

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