Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

11 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Graham,

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

Did you try count(distinct . . .) ?

What are the results without any selection ?

RR

Not applicable
Author

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

Not applicable
Author

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

lironbaram
Partner - Master III
Partner - Master III

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

Not applicable
Author

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

Not applicable
Author

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