topic Re: How to combine Three dates and three amounts in QlikView

How to combine Three dates and three amounts

Anonymous — Thu, 12 Jul 2012 13:55:35 GMT

I am trying to calculate Gross Loss. Gross Loss = Total Chargeoffs - (Total Restitution + Total Payments). Each of these amounts is located in a different table with different date name, Charge off Month, Recovery Month and Payment Month. I have loaded all of the appropriate tables into Qlikview and created a pivot for each piece of information. I tried to create a pivot where my expression is Sum (ChargeOffAmount) - (Sum (RecoveryAmount) + Sum (PaymentAmount)), but because they use different date fields, the totals are incorrect.

Any suggestions will be greatly appreciated.

Re: How to combine Three dates and three amounts

Thu, 12 Jul 2012 13:58:14 GMT

you should either convert dates from both tables in load script (better idea)

use calculated dimension

send the qvd or table files

Re: How to combine Three dates and three amounts

Anonymous — Thu, 12 Jul 2012 14:02:44 GMT

Hi PariPari,

By convert, do you mean, alias them to be the same?

Re: How to combine Three dates and three amounts

Thu, 12 Jul 2012 14:11:18 GMT

can you sand a sample of these tables (i am not sure i understand you - my eanglish is week )

Re: How to combine Three dates and three amounts

Anonymous — Thu, 12 Jul 2012 14:16:46 GMT

do I need to attach the qvw to my discussion or send the file to you directly? i am very new to this forum.

Re: How to combine Three dates and three amounts

CELAMBARASAN — Thu, 12 Jul 2012 14:25:34 GMT

Hi,

Its better to provide the structure of the tables to solve the problem which is helpfull to understand it easily

Celambarasan

Re: How to combine Three dates and three amounts

Anonymous — Thu, 12 Jul 2012 14:34:20 GMT

Understood..here are my load statements for the 3 tables in question...



 CHARGEOFFS:
LOAD 
 CASEID,
 CHARGEOFFAMOUNT,
    CHARGEOFFDATE,
    CHARGEOFFGL,
    CHARGEOFFSETRU,
    INCIDENTCOUNTERID,
    LOCATIONID,
    STARTDATE,
    CATEGORYID,
    Month(CHARGEOFFDATE) as ChargeOffMonth,
    Date(CHARGEOFFDATE,'MMM-YYYY') as ChargeOffMonthDate,
    Date (monthstart (CHARGEOFFDATE) , 'MMM_YYYY') as ChargeOffMonthYear;
SQL SELECT 
 CASEID,
 CHARGEOFFAMOUNT,
    CHARGEOFFDATE,
    CHARGEOFFGL,
    CHARGEOFFSETRU,
    INCIDENTCOUNTERID,
    LOCATIONID,
    STARTDATE,
    CATEGORYID
FROM ICMSCITRIX.dbo.TBLINCIDENT
WHERE CHARGEOFFDATE >= '1/1/2012' 
AND CHARGEOFFDATE <= '12/31/2012'
AND CHARGEOFFGL IN (860360, 980360, 990360, 970360, 860500);
 
 
RECOVERY:
LOAD CASEID,
    DATEOFRECOVERY,
    ITEMIZEDRECOVERYGL,
    ITEMIZEDRECOVERYLOSSLOCATION,
    TOTALRECOVERY,
    Month(DATEOFRECOVERY) as RecoveryMonth,
    Date (monthstart (DATEOFRECOVERY) , 'MMM_YYYY') as RecoveryMonthYear;
SQL SELECT CASEID,
    DATEOFRECOVERY,
    ITEMIZEDRECOVERYGL,
    ITEMIZEDRECOVERYLOSSLOCATION,
    TOTALRECOVERY
FROM ICMSCITRIX.dbo.TBLITEMIZEDRECOVERY
WHERE DATEOFRECOVERY >= '1/1/2012'
AND  DATEOFRECOVERY <= '06/28/2012'
AND  ITEMIZEDRECOVERYGL IN (860360, 980360, 990360, 970360, 860500);
 
 
 
PAYMENTS:
LOAD 
 CASEID,
 INVESTIGATORID,
    PAYMENTAMOUNT,
    PAYMENTDATE,
    Month(PAYMENTDATE) as PaymentMonth,
    Date(PAYMENTDATE,'MMM-YYYY') as PaymentMonthDate,
    Date (monthstart (PAYMENTDATE) , 'MMM_YYYY') as PaymentMonthYear;
SQL SELECT 
 CASEID,
 INVESTIGATORID,
    PAYMENTAMOUNT,
    PAYMENTDATE
FROM ICMSCITRIX.dbo.TBLRECOVERYPAYMENT
WHERE PAYMENTDATE >= '1/1/2012'
AND PAYMENTDATE <= '12/31/2012';

Re: How to combine Three dates and three amounts

Thu, 12 Jul 2012 14:52:39 GMT

can't you alias those 3 dates the same way?

or you can load all three tables into one table and add column like: type (where you put one of the string 'Charge', 'Recovery' and 'Payment' every row)

Re: How to combine Three dates and three amounts

Anonymous — Thu, 12 Jul 2012 15:41:52 GMT

I did that and it appears to be working, but now I have a synthetic key because MonthYear appears in 3 tables.

Re: How to combine Three dates and three amounts

Thu, 12 Jul 2012 15:45:54 GMT

it is a good practice to make a seperate table for calendar (move month, year, week....into it)

Re: How to combine Three dates and three amounts

Anonymous — Thu, 12 Jul 2012 15:46:52 GMT

I will try that as well. Many thanks for your suggestions.