QlikView decides to consolidate wrong data? bug??!

[#1]

I have two records:

2013-06-20 00:00:00.000

0001733465

2013-08-06 00:00:00.000

R001733465

When I want to show this in Qlikview Pivot or even just a normal List box, it just shows 0001733465!

When I show the dates it shows THE SAME NUMBER FOR BOTH DATES

WTF is going on? My boss just asked me if we can trust my data, and my data is fine, QlikView is screwing around and this is a serious issue!

[michael_maeuser · Accepted solution]

if you want qlikview to load it exactly the way it is shown above, try to load this field as text(fieldname)

[michael_maeuser]

if you need only the date try date(left(fieldname, 10)) as fieldname

[#4]

There's obviously more fields than that but because it's sensitive data I can't post it here.

Here is what I load in my QVW:

SQL select * from QV_ERP_Costing_Report_V where CALLNBR in ('R001733465','0001733465');

It yields the 2 records above, the only column that's the same is the purchase order number because it defaults to blank if you don't put anything in on our ERP system.

It physically shows 2 entirely separate distinct unique records in SQL Server 2008. Why is QlikView trying to consolidate it just because ONE column is SORT OF the same?!

[Anonymous]

If there are no selections, list box shows the data exactly as it was loaded.  See if you have something in the script, so the second line either is not loaded at all or modified on load.

Edit (after seeing the SQL):

Are you sure that the value is exactly 'R001733465'?

Try this:

SQL select * from QV_ERP_Costing_Report_V

where CALLNBR like '%001733465%'

[#6]

On my dimension I did =text(CALLNBR) and it still only shows 1 number, not 2 distinct values

Imagine my financial director when he finds out the qlikview reports are consolidating/summarizing accounts incorrectly!

[#7]

Nothing is modified at all. I have a view in SQL Server. The load statement is:

SQL select * from QV_ERP_Costing_Report_V where CALLNBR in ('R001733465','0001733465');

This gives me back only 2 records and there are no selections active (pressed clear button)

On loading, it says "Costing << QV_ERP_Costing_Report_V 2 lines fetched"

But it only shows 1 CALLNBR for BOTH records

The ITEMDESC / QTY / Extended Price etc are unique PER record

[rbecher]

As Michael mentioned you will probably need a preceding LOAD statement with text() function an that field to prevent QlikView from handling this as a number:

LOAD text(CALLNBR) as CallNbr, ... ;

SQL SELECT * FROM ...

- Ralf

[Miguel_Angel_Baeyens]

Hi Pieter,

For some reason, QlikView is reading two lines and keeping both values in "Manifest" as numeric. My guess is that the "R" preceding the code is a money symbol or something similar that keeps QlikView as interpreting the value as a numeric value.

Check the attached applications that show that behaviour. Using Text() in the LOAD statement for those fields or modifying environment variables (I wouldn't recommend the latter, though) would do.

Hope that helps.

Miguel

[jonathandienst]

Pieter

I assume that this is South African data, where R is the currency symbol for SA Rand. Qlikview determines the data type from the first record it loads in a field, and then attempts to conform the following data to the same format. If it loads the R record first, it is deciding (like Excel) that this is an amount in Rand, formatted as R000000000. It then loads the second record the same way. As the numerical portion is the same, it sees them as a duplicate record and will only show one in a table box (which is one reason to not use a table box!!) and one record in a list box.

The same happens if it loads the other record first, except that it now applies a format of 0000000000. Because the second record can be read as a number (because it looks like an amount in Rand), it is formatted the same way as the first record (ie As 0001733465).

That's the why. The fix is to make QV load teh fields as text, like this:

LOAD Text(CALLNBR) as CallNumber, *;

SQL select * from QV_ERP_Costing_Report_V where CALLNBR in ('R001733465','0001733465');

Now use CallNumber rather than CALLNBR.

Hope that helps

Jonathan