topic Column problem - splitting column ? in QlikView

Column problem - splitting column ?

Wed, 09 Sep 2009 17:34:08 GMT

I have a problem with a table which contains financial information (debit and credit). Whereas there is a single column for debit account number and one for credit account number, there is only one column for amount and they are all positive amounts. I need to calculate in pivot or straight table the positive amount for the debit account and negative amount for the credit account. My problem is that all amounts have account numbers in both debit and credit (ofcourse any finance people would say) and they are never the same for one transaction. But some of the account numbers are to be found both in debit and credit but not on the same transaction.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/7331.Account.doc:550:0]

In the enclosed you can see table structure from column H to K and how it shall look in pivot or straight table in column M to O.

Any ideas ?

Column problem - splitting column ?

bismart — Wed, 09 Sep 2009 19:51:14 GMT

Just load data twice as follows;

Transactions:

Load

DEBIT_ACCNT as Account,

AMOUNT as Debit,

0 as Credit

from Table A;

Load

CREDIT_ACCNT as Account,

0 as Debit,

AMOUNT * -1 as Credit

from Table A;

Column problem - splitting column ?

Wed, 09 Sep 2009 21:20:17 GMT

Unfortunately that dosen't do it. In order to avoid having syntethic keys I have to name the "0 as Credit/0 as Debit" to something else otherwise Credit and Debit exists in both tables and are trying to link.

Furthermore, I only get data where both Accounts are equal - not the ones where there is only credit account or debit account. In addition the figures are wrong (sum).

Column problem - splitting column ?

bismart — Wed, 09 Sep 2009 21:54:08 GMT

The second load statement loads the records into the same table 'Transactions' , once the column names are the same in bothload statements.

Tiy should only have 1 table in your application after using suggested code

Column problem - splitting column ?

johnw — Thu, 10 Sep 2009 06:39:21 GMT

Sign was wrong on the amounts. Looked like the only problem to me. And while not necessary, I prefer to explicitly say which table I'm concatenating to. See attached.

Column problem - splitting column ?

Thu, 10 Sep 2009 14:08:20 GMT

Thank you ever so much both of you !!

With at few ajustments so it fitted my database I got it to work with correct figures and everything. You have just saved me days of headache.

John Witherspoon - your enclosed example was very helpfull and put me in the right direction and way of thinking. A special thanks to you for always providing good example qvw's.

Column problem - splitting column ?

bismart — Thu, 10 Sep 2009 15:11:15 GMT

Can't see a problem with my amounts... Credit accounts hold negative values as per original document.

Maybe I'm going a bit daft and still think my solution is cleaner and easier... but hey ho , more than one way to skin a cat

Column problem - splitting column ?

johnw — Fri, 11 Sep 2009 00:11:34 GMT

Ah, gotcha. Then MY example has the sign backwards. Sorry to have "corrected" you in error.

And "my" solution was simply to demonstrate that YOUR solution worked. I think the only change I made, other than screwing up the sign, was to add CONCATENATE([Split]). I agree that this line isn't necessary, as that is exactly what QlikView will do by default. However, I like to say it explicitly. I think it is more clear to less experienced developers reading the code, and it also allows me to insert a different load in the middle without breaking anything. I've seen developers do exactly that, and then ask me why their data was all messed up. So it's just a safety measure. Definitely overkill here, but just something I've made a habit of.

Column problem - splitting column ?

bismart — Fri, 11 Sep 2009 14:58:48 GMT

Thank you sir... you're a gentleman.

Wasn't being awkward... just thought I was going a bit mad for a moment

Cheers