Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
greend21
Contributor II

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

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.

Tags (3)
1 Solution

Accepted Solutions
greend21
Contributor II

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

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

6 Replies
Partner
Partner

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

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
Honored Contributor II

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

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
Contributor II

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

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
Contributor II

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

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
Contributor II

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

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
Contributor II

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

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