Guidance: Difference is Value

[pkelly]

Hi all...

Looking for some guidance...

I have an extract from my sales invoice warehouse for the month of August 2010.

As a starting point, I created a pivot table and squared both the sales and gross profit values with a previous report - squared ok.

User wants a data dump so I created a table box and populated with the required fields.

Dumped this out to MS Excel and totalled both the sales and gross profit values with the intention of squaring.

The MS Excel total differs from the pivot table values.

There are no reference tables - both tables are working from a single table.

Can anyone give me an insight into why both would not give the same totals..?

Any assistance greatly appreciated...

Regards

Paul

[johnw]

Are they very close, but not exact? Could be differences in internal format and/or precision used for calculations. QlikView isn't particularly precise, and uses binary floating point. Not sure what Excel does. If they differ, then when QlikView squares, it could have some minor issues, and Excel could either get it right or have different minor issues.

Have you manually verified which one is correct?

[pkelly]

Hi John

The pivot table values square with a different report, so I am happy with the pivot table totals...

Sales are out by £174k, Gross Profit by £25k so quite a significant difference with the table box being down.

Just to qualify that it is a "Table Box " and not a "Straight Table" that I am using.

Did another test using a "Straight Table" and the values here square with the pivot table.

Confused...

[pkelly]

Hi John

Did a data dump from the Straight Table and compared with the Table Box (pivoted in Excel) and have managed to pinpoint the transactions which have different values...

Will need to dig further and try and work out why ...

[pkelly]

Right...think I know what it is doing...

Warehouse data....

Order #	Item #	Product	Value
1	1	MDFA	150
1	2	MDFA	150
2	1	MDFA	175
2	2	MDFB	200

Data Import ...

Order #		Product	Value
1		MDFA	150
1		MDFA	150
2		MDFA	175
2		MDFB	200

Table Box..

Order #	Product	Value
1	MDFA	150
2	MDFA	175
2	MDFB	200

This is obvioulsy a very simplified example but it looks like the table box is working its way down the records and removing any that it thinks is a duplicate? hence only 1 record shown for order #1...

Is this a bug or expected result?

[johnw · Accepted solution]

Paul Kelly wrote: ...it looks like the table box is working its way down the records and removing any that it thinks is a duplicate?...Is this a bug or expected result?

Expected result. Table boxes remove duplicates. To show all of the rows in your table, your table should have a unique ID for each row, and this ID should be in your table box. Technically, you can do it without a unique ID using a straight table and some complexity, as per the thread below, but I strongly recommend just using a unique ID.

http://community.qlik.com/forums/t/32954.aspx