Incorrect Count value

[#1]

Hi,

I am loading some data from a QVD with the following load script:

AgingReports:

LOAD DEALID AS ISSUER_ID,

     DEAL as ISSUER_NAME,

     ISSUEID,

     ISSUE,

     CLASSID,

     CLASS,

     BUSINESSGROUP as PRIMARY_ANALYST_GROUPS,

     PRIVATEPLACEMENTID,

     RATINGCODE as RATING,

     Date(RATINGDATE, 'DD-MM-YYYY') as RATING_EFFECTIVE_DATE,

     Year(RATINGDATE) as EFF_DT_YEAR,

     Month(RATINGDATE) as EFF_DT_MONTH,    

     RATINGTYPEDESCRIPTION as RATING_TYPE,

     RATINGACTION,

     RATINGID,

     MARKET_SECTOR_2_DESC & '(' & MARKET_SECTOR_2_ID & ')' as ISSUER_MARKET_SECTORS,

     MARKET_SECTOR_4_DESC & '(' & MARKET_SECTOR_4_ID & ')' as ISSUER_MARKET_SECTORS_4,

     ENTITYCOUNTRYNAME as ISSUER_LOCATION,

     ENTITYGEOSEGMENT as ISSUER_REGION,

     FULLNAME as PRIMARY_ANALYSTS,

     ANALYSTCOUNTRYNAME,

     len(FULLNAME) as LenPA,

     ENTITYREGION,

     ANALYSTGEOSEGMENT,

     ANALYSTREGIONNAME,

     if (date(RATINGDATE)<=date(AddYears(Today(),-1)),'True','False') as Overdue,

     if (date(RATINGDATE)>=date(AddYears(Today(),-1)) and date(RATINGDATE<=date(AddYears(Today(),-1))+90),'True','False') as Overdue90,

     if (date(RATINGDATE)>=date(Today())-275,'True','False') as Reviewed275,

     Addmonths(RATINGDATE,12) as OverdueThreshold,

     num(Today() - Addmonths(RATINGDATE,12),'#,##0') as overdueby,

     num(Today() - Addmonths(RATINGDATE,12) + 7,'#,##0') as overdue1week,

     num(Today() - Addmonths(RATINGDATE,12) + 14,'#,##0') as overdue2week,

     if (date(RATINGDATE)>=date(AddYears(Today(),-1))-35 and date(RATINGDATE)<=date(AddYears(Today(),-1)),'True','False') as Overdueby1to5week,

     if (date(RATINGDATE)>=date(AddYears(Today(),-1))-70 and date(RATINGDATE)<=date(AddYears(Today(),-1))-35,'True','False') as Overdueby6to10week,

     if (date(RATINGDATE)>=date(AddYears(Today(),-1))-140 and date(RATINGDATE)<=date(AddYears(Today(),-1))-70,'True','False') as Overdueby11to20week,

     if (date(RATINGDATE)<date(AddYears(Today(),-1))-140,'True','False') as Overduebygt20week,

     if (date(RATINGDATE)>=date(AddYears(Today(),-1)) and date(RATINGDATE)<=date(AddYears(Today(),-1))+35,'True','False') as Duein1to5week,

     if (date(RATINGDATE)>=date(AddYears(Today(),-1))+35 and date(RATINGDATE)<=date(AddYears(Today(),-1))+70,'True','False') as Duein6to10week,

     if (date(RATINGDATE)>=date(AddYears(Today(),-1))+70 and date(RATINGDATE)<=date(Today()),'True','False') as Dueingt10week     

FROM

    [..\..\..\QVDATA\AGINGREPORT\SF\AGINGREPORTDATASET.qvd] (qvd);

I created a simple straight table with all of the columns in the straight table. I then filtered the recordset via the value

PRIMARY_ANALYSTS and this returns me a single row of data as expected.

I then created an expression to show the number or records where a condition is met:

Count({$<Reviewed275={'True'}>} Reviewed275)  (i.e. count the total number of records where Reviewed275 has a value of True)

I expect the count to be 1 as I have already filtered the data by PRIMARY ANALYST and the row in the straight table has a

Reviewed275 value of True.

However, when the expression is calculated I am getting a value of 4 which is obviously not correct.

Does anyone have any ideas on what the issue may be?

Many Thanks

Graham

[#2]

Hi,

thanks for your collective help, I can see that the problem of the duplicates is coming from the left join process on the QVD tier which is not working as it should i guess. I am still able to get around the problem by using distinct loads and the counts are now working fine.

Many thanks for all the help

Graham

[lironbaram]

hei yes usualy the duplication of records is happenning when you use left join

so yes i would think your record duplications is from the construction of the qvd file