Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Graham,
is the field "Reviewed275" a link to another table ?
Did you try count(distinct . . .) ?
What are the results without any selection ?
RR
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
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
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
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
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