topic Re: Issue in Qlikview new version in QlikView

Issue in Qlikview new version

Thu, 29 Oct 2015 11:46:16 GMT

When I try to load the data from two table

one with ID 024 and other with 24

it give the first appearance of the data i.e. if 024 present in first table it will give 024 and exclude 24 ,if it is 24 present in first table it give 24 and exclude 024.

Any solution to this , how we get both the data

Re: Issue in Qlikview new version

rubenmarin — Thu, 29 Oct 2015 11:48:30 GMT

Hi, load those values using Text():

LOAD Text(ID) as ID

...

Re: Issue in Qlikview new version

Anonymous — Thu, 29 Oct 2015 11:50:18 GMT

Well,

May be at UI level it is getting concatenated with your first appeared value.

But queation is: are you getting all the values of 024 and 24 under first appearance of any of them?

Re: Issue in Qlikview new version

Anonymous — Thu, 29 Oct 2015 11:51:14 GMT

or try Text() for this field

Re: Issue in Qlikview new version

Thu, 29 Oct 2015 11:54:08 GMT

yes

Re: Issue in Qlikview new version

Thu, 29 Oct 2015 11:57:27 GMT

it works

Re: Issue in Qlikview new version

Anonymous — Thu, 29 Oct 2015 12:03:13 GMT

Good, then you want both?

May be u r using num() at ETL level?

If you want both the value, then try Text()

Re: Issue in Qlikview new version

Thu, 29 Oct 2015 12:45:19 GMT

but prior to this it was working fine

Re: Issue in Qlikview new version

Thu, 29 Oct 2015 12:46:20 GMT

what is the reason for this issue

Re: Issue in Qlikview new version

rubenmarin — Thu, 29 Oct 2015 12:48:10 GMT

Hi shweta, and it was the same source? something like this can happen when before was readed from a source wich default data type is string (text) but now is reading as default data type as number, and '024' and '24' are the same number.

Re: Issue in Qlikview new version

jonathandienst — Thu, 29 Oct 2015 13:02:58 GMT

Qlikview attempts to recognise data based on the first (or first few) record(s) - in a similar way to Excel. If the record looks like a number (and both 024 and 24 look like numbers), the QV will load the data as numbers, and clearly 024 and 24 are the same number. The Text() function at the first load point ensures that the data is retrieved as text - the leading 0 will be preserved and the two IDs will be different.

This is why is is usually considered bad practice to use leading zeroes and leading spaces in code/id/category fields.

Re: Issue in Qlikview new version

Fri, 30 Oct 2015 04:22:15 GMT

but how it is possible that it was working till 5th oct 2015 it show the records for both the ID's .

But now it give data for 024 (i.e. first appearance)

We are fetching the data from sql database

Re: Issue in Qlikview new version

petter — Fri, 30 Oct 2015 06:43:02 GMT

One possible explanation could be that it was actually O24 stored before 5th Oct 2015 and someone changed it into 024 on the 6th of October... It is really hard to guess at what happened in your source systems at a point back in time.

But there are certainly pitfalls of data quality with the load script in this area. Especially ID's that are really not numeric but they look like it. That is they come from character columns in databases and should actually be treated as pure text values in QlikView. If you don't do that you potenially get corrupt data. I am not kidding. It will interpret separate distinct differently character/text ID's as the same value based on the numeric interpretation. This is usually not at all what you want.

That is why it is adamant that you wrap all such columns in the Text()-function while loading them to tell QlikView to keep them as Text and only Text. If not some or all values - depending on whether they look like numbers or not - will be Dual values and not Text values.

That will lead to all sorts of Quality issues:

1. Incorrect counts

2. Missing IDs (often product IDs) - as some distinct IDs will be treated as the same

3. Sorting is quite different when it is dual than the sorting is with pure Text values.

In enterprise implementation I tell my customers ALWAYS to wrap columns by using Text() when doing a load from any source when it comes to character columns. If you don't you might be lucky and not run into the problem. But it is a sure land mine that the system sooner or later will run into. And it is a particular NASTY problem as it is not so easy to detect. Just a few IDs will silently disappear and be counted and aggregated in the wrong way.

So a correct approach would be to have a load statement like this:

Datatable:

LOAD

Text( ID ) AS ID,

Text( ProductCode ) AS ProductCode,

Text( Vendor ) AS Vendor,

Num( Quantity ) AS Quantity

;

SQL

SELECT ....... ;

Yes I know it is "boring" to have to write Text() always for every character column that will be turned into a field. But if you don't it will lead to data quality issues unless you are extremely lucky.

That is why I made a routine that automatically generate the load script for the first tier of import of data so my customers and I don't need to remember this stuff.