Data mulitiplies

[#1]

We have two tables with different data. Alle cases are in Action_Comments (actions) and some of them are also in LedgerCase (payments). I need to find out how long after a latest action a payment has been made.

My problem is that as the action can be in Action_Comments several times on same or different dates the payments are multiplied.

Action_Comments

Case_id	Linie no	Action Date	Action Code
1408220	40	14-01-2009	U45
1408220	42	15-01-2009	F46
1408220	43	17-01-2009	BABA
1408220	44	21-01-2009	U45
1408220	45	21-01-2009	U45
1408220	46	21-01-2009	U45
1408220	47	21-01-2009	U45
1408220	48	21-01-2009	U45
1408220	49	01-03-2009	KFRI
1408220	52	01-09-2009	U45
1398438	49	02-01-2009	U45
1408255	50	21-01-2009	U45
1435884	51	21-01-2009	U45
1435884	52	01-09-2009	U45

LedgerCase

CA_CASE_ID	AMOUNT	DEBIT_ACCT	CREDIT_ACCT	REG_DATE	GIRO_DATE	AMOUNT_ID
1408220	789,00	1170	2103	10-09-2009	07-09-2009	3910745
1408220	500,00	1170	2103	10-11-2009	07-11-2009	3910746

The sample here for Case_id 1408220 should give the following result:

ACTION_CODE
U45	Paid within 10 days after action	kr 789,00
U45	Paid between 10 and 20 days after action	kr 0,00
U45	Paid between 20 and 30 days after action	kr 0,00
U45	Paid between 30 and 45 days after action	kr 0,00
U45	Paid between 45 and 60 days after action	kr 0,00
U45	Paid more than 60 days after action	kr 500,00
U45	Paid in total	kr 1.278,00
U45	Paid before action	kr 0,00

I have managed to get the max date per Action_Code but the figures are still multiplied.

Here is a sample of my load script and expression for Paid within 10 days after action:

Load script:

Action_Comment:

LOAD

Comment_Case_id as Case_Case_id,

RowNr,

COMMENT_DATE as Action_COMMENT_DATE,

ACTION_CODE

FROM ActionComments.xls (biff, embedded labels, table is Sheet1$);

join(Action_Comment)

load

Case_Case_id,

ACTION_CODE,

Max(date(date(Action_COMMENT_DATE, 'DD-MM-YYYY'))) as endPeriod

resident Action_Comment

group by Case_Case_id,ACTION_CODE;

Expression:

sum(

(if(LedgerCase_RegDate>(endPeriod) and (LedgerCase_Account=1170 or LedgerCase_Account=1173 or LedgerCase_Account=1175)

and (LedgerCase_RegDate-(endPeriod)<=10)

,LedgerCase_DebitAmount)))

Any help will be greatly appreciated.

[#2]

Your Action_Comment table holds all the records, i.e. multiples. By joining to this table each multiple record now gains the max( ) value, and is therefore multiplied.

Create the endPeriod field as

if(Action_COMMENT_DATE = Max(date(date(Action_COMMENT_DATE, 'DD-MM-YYYY'))),Action_COMMENT_DATE,0) as endPeriod

so only one record in Action_Comment gets a value.

Not sure if this is correct; you seem to have too many date( ) functions. Perhaps I have not got enough.

[#3]

Thanks for the input but that dosen't solve the problem. As there is Action_Comment_Date in your suggestion I need to include it in the Group by which means that the endPeriod is shown per unique occurance.

[Oleg_Troyansky · Accepted solution]

In your resulting table you will have multiple rows holding the same end_Period. When you compare Ledger Date with end_Period, QlikView will evaluate each available record and therefore multiply the numbers.

I think the solution is creating a summarized table with a composite key and hold end_Period separately, once for each Case and Action. This way, you will be able to avoid multiplying the numbers.

cheers,

[#5]

The solution was indeed to hold end_period seperately. So instead of joining it the solution was jus to load max date (end_period) in a seperate table. Thanks to all of you for input.

[#6]

The solution was indeed to keep the end_period seperately. So loading it instead of joining and thus keeping it in a seperate table