Possible join issue. Pivot showing the same count for two fields

[greend21]

I have two different systems that house policy information and I have information to get the counts of in force policies as of the last day of each month. When I set up a pivot table it is showing the same counts each month for each system under both system A and system B. The InforceCounts table has BillSysCd in it to distinguish the systems so I do not understand why they are not being correctly broken out. Could it be my join?

Here is my loadscript and view of the relation in the tableviewer.

InforceCounts:
LOAD BillSysCd,
InFPolicyNumber,
FarmFlag as FarmFlagIn,
ELSFlag as ELSFlagInF,
MCD as MCDInF,
PolEffDt,
Month(PolEffDt) as PolEffDtMonth,
PolExpDt,
AmtGross,
AmtNet
FROM $(vQVDPath)InforceCounts.qvd (qvd);

Left Join(InforceCounts)
Load InFPolicyNumber,
IFDate,
Month(IFDate) as MonthIF,
Year(IFDate) as InFYear
From $(vQVDPath)IFReferenceTable.qvd (qvd);

InForceIF:
Load
IFDate,
If((IFDate>=AddMonths(Monthstart(Today()),-12)) and (IFDate<=MonthEnd(Today())),1,0) as InFRoll13Flag,
If(PolEffDt<=IFDate and IFDate<PolExpDt,1,0) as InFPolicyFlag
Resident InforceCounts

Expression: count({$<InFRoll13Flag={1}>}InFPolicyFlag)

Also, do you know why sum and count would show different totals? Summing and counting the fields marked with 1's should show the same totals.

[martinpohl]

hello Dan,

please check the number of rows after load from qvd and after join.

for those data I can see in script, you do not have to join. A linked table works also.

it looks so that some inpolicynumber have many sates. so for that you have one count in total but many (two) in sum.

check your data.

regards

[devarasu07]

Hi,

try to use distinct with in your set analysis expression,

count({$<InFRoll13Flag={1}>} distinct InFPolicyFlag)

how can we say sum and count results should be same?

it should give different results, lets say example,

Product,Sales

A,10

B,20

then product count is 2 ( i.e count (distinct Product) )

similarly, product sum is 30 (i,e sum( sales) )

also check this thread,

A Myth About Count(distinct …)

Thanks,

Deva

[greend21]

I did just realize that using count is also going to count 0's, isn't it. I was thinking because the field is either 0 or 1 that it should be the same but wasn't condisering that 0's are being counted. The way I am using Iterno to generate dates though I would think that the only values would be a 1 though.

[greend21]

Don't I need to join since one of the IF statements in the last table includes fields from both the first and second table? PolEffDt and PolExpDt are from table 1 and IFDate is from table 2.

InForceIF:

Load
IFDate,
If((IFDate>=AddMonths(Monthstart(Today()),-12)) and (IFDate<=MonthEnd(Today())),1,0) as InFRoll13Flag,
If(PolEffDt<=IFDate and IFDate<PolExpDt,1,0) as InFPolicyFlag
Resident InforceCounts

How would I check the number of rows at those two points?

[greend21]

I put RowNo() in the script and commented out the 2nd and 3rd tables and came to 91,546 as my max row number. I uncommented the tables and ran it again and had the same max.

[greend21 · Accepted solution]

For anyone else that has this issue, I had to add the policy to the 3rd table and calculate the Month and Year down there. Here is the new script.

InforceCounts:
LOAD BillSysCd,
InFPolicyNumber,
FarmFlag as FarmFlagIn,
ELSFlag as ELSFlagInF,
MCD as MCDInF,
PolEffDt,
Month(PolEffDt) as PolEffDtMonth,
PolExpDt,
AmtGross,
AmtNet
FROM $(vQVDPath)InforceCounts.qvd (qvd);

Left Join(InforceCounts)
Load InFPolicyNumber,
IFDate
// Month(IFDate) as MonthIF,
// Year(IFDate) as InFYear
From $(vQVDPath)IFReferenceTable.qvd (qvd);

InForceIF:
Load InFPolicyNumber,
IFDate as InFDate,
Month(IFDate) as InFMonth,
Year(IFDate) as InFYear,
If((IFDate>=AddMonths(Monthstart(Today()),-12)) and (IFDate<=MonthEnd(Today())),1,0) as InFRoll13Flag,
If(PolEffDt<=IFDate and IFDate<PolExpDt,1,0) as InFPolicyFlag
Resident InforceCounts