11 Replies Latest reply: Nov 24, 2011 6:21 AM by Liron Baram RSS

    Incorrect Count value

      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

        • Incorrect Count value

          Hi Graham,

           

          take care when checking data using straight tables. A straight table shows only unique rows. For this reason you can't find duplicate records with(in) a straight table. The point of truth is the use of count() similar as you did. May be in a simple textbox.

           

           

          HtH

          Roland

            • Incorrect Count value

              Hi Roland,

               

              Thank you for your advice, i have the same results in a textbox also only 1 row of data is there but the count is still 4 for that particular selection.

               

              Thanks

               

              Graham

                • Incorrect Count value

                  Graham,

                   

                  is the field "Reviewed275" a link to another table ?

                  Did you try count(distinct . . .) ?

                  What are the results without any selection ?

                   

                  RR

                    • Incorrect Count value

                      Hi Roland,

                       

                      the field comes from the load script:

                       

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

                       

                       

                      When the selection is removed the count increases as it starts to count the True value for all

                      records in the recordset but the number is still wrong as there are duplicate counts across

                      all rows.

                       

                      I am going to create a version of the file that I can upload here to test with.

                       

                      thanks

                       

                      Graham

                        • Re: Incorrect Count value

                          I have gone a step further in this by filtering the dataset directly in the load script and interestingly it is bringing back four rows where i apply the filter in load script.

                           

                          If i look in the table viewer and then preview rows I can see four rows, however when the fields are all added to a tablebox only 1 row is displayed. Any ideas why this would be the case? I have attached the file to try and help out in finding out why I can only see 1 row in the table box.

                           

                          thanks

                           

                          Graham

                            • Incorrect Count value
                              Liron Baram

                              hei if a all the fields have the same vlaue in them then a table box will show only one row

                              i you dont have a uniqe key add in the load script the field rowno() then you will see the real number of rows

                                • Re: Incorrect Count value

                                  Hi,

                                   

                                  adding the rowno() does now allow the four rows to be shown in the table box, so thank you for that. I am not sure where those duplicates are coming from though as there are none in the data. Is it possible that in the construction of the qvd's, using left joins to pull together some qvd files is causing duplicate entries to be created?

                                   

                                  thanks

                                   

                                  Graham

                                • Re: Incorrect Count value

                                  Hi again,

                                   

                                  same thing is valid for tableboxes: QV "optimizes" duplicate records and shows only one of them. For my oppinion at least a table box should show all records regardless being duplicates, but as I told you. . .

                                   

                                  Hint:

                                  add "rowno() AS MyID" to get a unique record ID.

                                   

                                  RR

                                    • Re: Incorrect Count value

                                      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

                            • Incorrect Count value

                              Hi again,

                               

                              in addition to my post above, it might be misleading.

                              "A straight table shows only unique rows." Better --> A straight table shows duplicate records only once.

                               

                              RR