topic Re: Combining transaction amounts (credit and debit)? in QlikView

Combining transaction amounts (credit and debit)?

Anonymous — Thu, 06 Feb 2014 00:02:05 GMT

Hello,

I'm not quite sure how to phrase this so I've given an example below.

I'm having issues combining amounts for a location where inventory is moved from one section to another and a transaction occurs much like a debit and credit (a positive amount and a negative amount net 0)

I have a user who would like to see a location, or section, and see the transactions associated with it.

For example, a user would like to pick Loc1 and see the net of the amount moved and each transaction whether the section is a source or destination. The example below would net 150 as the transactions on 1/21 would cancel each other out and I would be left with the amount on 2/1. The amounts, however, are always positive and if I simply used a SourceSection or DestSection as a selection criteria, I would be left with wrong data as amounts would not cancel each other out.

Location	TransNo	SourceItem	SourceSection	DestItem	DestSection	Date	Amount
A1	1	Item1	Loc1	Item2	Loc2	1/21/14	150
A1	2	Item2	Loc2	Item1	Loc1	1/21/14	150
A1	3	Item1	Loc1	Item2	Loc2	2/1/14	150

I'm thinking I need a "master" table and then split the table above into a source and destination table, but I'm not quite sure how to go about doing this.

Any help would be greatly appreciated.

Thank you in advance.

Re: Combining transaction amounts (credit and debit)?

maxgro — Thu, 06 Feb 2014 00:54:56 GMT

just un idea, hope it helps

Re: Combining transaction amounts (credit and debit)?

iktrayanov — Thu, 06 Feb 2014 00:56:06 GMT

Maybe you should create a table like this

Location,

TransNo,

Item,

Section,

Amount,

Date,

Credit_DebitFlag \\ this should be 1 or -1 and will give you a way to calculate your amounts

Re: Combining transaction amounts (credit and debit)?

iktrayanov — Thu, 06 Feb 2014 01:05:13 GMT

this is how you example data will look like.

Location	TransNo	Item	Section	Date	Amount	Credit_DebitFlag
A1	1	Item1	Loc1	1/21/2014	150	-1
A1	1	Item2	Loc2	1/21/2014	150	1
A1	2	Item2	Loc2	1/21/2014	150	-1
A1	2	Item1	Loc1	1/21/2014	150	1
A1	3	Item1	Loc1	2/1/2014	150	-1
A1	3	Item2	Loc2	2/1/2014	150	1

Re: Combining transaction amounts (credit and debit)?

Anonymous — Thu, 06 Feb 2014 12:32:16 GMT

Massimo,

This is perfect and exactly what I was looking to emulate. Thank you so much!