Record disparities between source and QV Chart

[Anonymous]

I am noticing some funny behaviour in my report generated as a straight table from a MSSQL Server data base source.  The report appears with some blanks as follows for the year 1961:

I queried the source data base to see what records are available for the year 1961 with the following results:

The 'A' was transformed at QV load to read 'Active' for QV reporting purposes.  The 418 record displayed in the SQL query above is the one in the straight table above.  However, i am surprised to see the blanks with '1961' Submission Dates which are not present in the source data base.  What could be going on?

Regards.

Chris

[Gysbert_Wassenaar]

My guess would be that you have another table in your qlikview data that also contains the field SUBMIT DATE. And perhaps you're using either a table box object instead of a straight table.

[Anonymous]

Many thanks, Gysbert let me look into it.

Regards.

[swuehl]

Are you creating a master table from field SUBMIT DATE in your QV load script?

I also assume it's a table box rather than a straight table. If my assumption in wrong, which are the dimensions and expressions used in your chart?

[Anonymous]

Dear Gysbert

I have checked my application, i am using a straight table and my model is simple and as follows:

Regards

Chris

[martinpohl]

as Gysbert wrote, in a tablebox the field SubmissionDate conatins all values from that field in all tables.

It seems that there is one record in FDAMFS on 01/03/1961 and the other records in table DMF Submission Calender.

One way is to load only those dates in DMF Subsmission Calender by

where exists (SubmissionDate) in QlikView script.

The other way is to omit rows where thefield i snull (porperties/presentation) activate by any other field than SubmissionDate from table FDAMFS

Regards

[swuehl · Accepted solution]

As far as I can see, there is no problem with your data model and data.

What do you want to show in your table?

If you want to show the content of the resident table FDADMS the same way as in your SELECT statement, there are multiple options to get rid of the extra lines resulting from your master calendar table:

- Create an additional key field in your master calendar and FDADMS, just a technical key field that links the table and is usually not used in charts or list boxes.

Then you can use all fields from FDADMS except that key field to build your table box or straight table.

Please note that QV is designed to show tables with aggregations, not to show just plain tables as stored in the data model with potential duplicate lines. So if your FDADMS table may contain duplicate lines, a table box or a straight table won't show these duplicate lines without some extra action (e.g. create a RecNo() field in your script or use an expression with a frequency count in a chart to indicate duplicates).

- As alternative to the extra key field, you can keep your current data model, but then you should create an expression in your straight table chart (I am assuming your are currently only using dimensions with 'suppress zero values' option disabled in presentation tab), that returns zero for all but the first line (like Count([DMF Number]) ).

Then enable 'suppress zero values' on presentation tab.

Again, I think your data model is ok, you just need to decide what you want to see / analyze in your table chart.

Hope this helps,

Stefan