topic Re: QlikView decides to consolidate wrong data? bug??! in QlikView

QlikView decides to consolidate wrong data? bug??!

Fri, 20 Nov 2020 18:02:30 GMT

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!

Re: QlikView decides to consolidate wrong data? bug??!

michael_maeuser — Thu, 29 Aug 2013 14:05:35 GMT

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

Re: QlikView decides to consolidate wrong data? bug??!

michael_maeuser — Thu, 29 Aug 2013 14:07:03 GMT

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

Re: QlikView decides to consolidate wrong data? bug??!

Thu, 29 Aug 2013 14:16:50 GMT

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?!

Re: QlikView decides to consolidate wrong data? bug??!

Anonymous — Thu, 29 Aug 2013 14:18:39 GMT

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%'

Re: QlikView decides to consolidate wrong data? bug??!

Thu, 29 Aug 2013 14:19:58 GMT

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!

Re: QlikView decides to consolidate wrong data? bug??!

Thu, 29 Aug 2013 14:23:33 GMT

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

Re: QlikView decides to consolidate wrong data? bug??!

rbecher — Thu, 29 Aug 2013 14:38:26 GMT

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

Re: Re: QlikView decides to consolidate wrong data? bug??!

Miguel_Angel_Baeyens — Thu, 29 Aug 2013 14:43:17 GMT

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

Re: QlikView decides to consolidate wrong data? bug??!

jonathandienst — Thu, 29 Aug 2013 14:46:51 GMT

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

Re: QlikView decides to consolidate wrong data? bug??!

jonathandienst — Thu, 29 Aug 2013 14:49:05 GMT

By the way, QV will have correctly loaded two rows, so Count(CALLNBR) should be two and Sum(CALLNBR) will be the same of the two.

Jonathan

Re: Re: QlikView decides to consolidate wrong data? bug??!

Miguel_Angel_Baeyens — Thu, 29 Aug 2013 14:58:20 GMT

Yes, that's pretty much it.

The variables at the beginning of the script tells QlikView how to load locale settings for dates, numbers and currency. In my case, although I'm Spanish, but my OS is in American English, MoneyFormat reads:

SET MoneyFormat='$#,##0.00;($#,##0.00)';

You can see the differences in the attached QVW above.

Moreover, if you change the load order, as both are numeric values, QlikView will show whatever comes first: the value starting in "0" or the one starting by "R".

So it was not QlikView messing anything, after all...

Miguel

Re: Re: QlikView decides to consolidate wrong data? bug??!

Fri, 30 Aug 2013 06:36:55 GMT

The R has nothing to do with currency, but I won't be surprised if this is why Qlikview is acting strange, let me check this

Re: Re: QlikView decides to consolidate wrong data? bug??!

Fri, 30 Aug 2013 06:42:47 GMT

I could only mark 2 answers as helpful, but this was also helpful. Thank you!