topic Re: Qlikview converting Text to Number and removing leading zeros in QlikView

Qlikview converting Text to Number and removing leading zeros

Tue, 15 Oct 2013 21:47:36 GMT

Hi All,

For following example:

Load * Inline [Account_Type

'20.001',

'20.00100',

'20.0010'];

How to insure qlikview shows up three values in List box or aggregated pivot.

What we are seeing is

result of above example is :

20.001

Therefore in list box it will display 1 row. If we try to aggregate any data, rather than 3 different account types, it aggregates to 1 row.

Please advise.

Regards

Ravi

Re: Qlikview converting Text to Number and removing leading zeros

rustyfishbones — Tue, 15 Oct 2013 21:51:18 GMT

You can use SUBFIELD

SUBFIELD([Account Type],',',) as [Account Type]

Regards

Alan

Re: Qlikview converting Text to Number and removing leading zeros

rustyfishbones — Tue, 15 Oct 2013 21:53:44 GMT

Should the Inline Statement not read like this

Re: Qlikview converting Text to Number and removing leading zeros

swuehl — Tue, 15 Oct 2013 22:15:45 GMT

Try

Load

text(Account_Type) as Account_Type

Inline [

Account_Type

'20.001',

'20.00100',

'20.0010'];

Re: Qlikview converting Text to Number and removing leading zeros

rustyfishbones — Tue, 15 Oct 2013 22:18:04 GMT

That works swuehl,

I was getting myself confused, I though it was a simple answer and it was

Thanks

Re: Qlikview converting Text to Number and removing leading zeros

Tue, 15 Oct 2013 22:23:47 GMT

Thanks , let me give it a go

Data example i provided was for reference purposes. Account Types we have are Alphanumeric , but some of those are just numeric and ending with decimal values.

Qkilview was removing leading zeros for those.

Let me try it out with large dataset and report back.

Regards

Ravi

Re: Qlikview converting Text to Number and removing leading zeros

Tue, 15 Oct 2013 23:39:55 GMT

In the use case, we are :

* selecting data from Oracle DB and then dumping it into QVD

* Then load from QVD to Dashboard

I tried " LOAD text(Account_type) as Account_Type..." didnt work.

What we are having to do now is, Add additional column Select statement of QVD generator such as ''''||Account_Type as Formatted_Account_Type this is appending single quote at start and then it shows data properly.

Sample data :

2725358199.001

2725358199.0010

No so happy with solution, what Qlikview is forcing us to do, adding a derived column to get around the issue.Qlikview doesnt seem to take into account data type aspect. Here our data type is VARCHAR2 at DB level there are two rows, and my belief QVD does have two rows (pivot /aggregation - show totals from both account_type), its just presentation part is where one goes missing.

Regards

Ravi

Re: Qlikview converting Text to Number and removing leading zeros

swuehl — Wed, 16 Oct 2013 07:49:02 GMT

You'll need to use the Load with the text() statement when loading from the DB source, applying it to the QVD Load is too late, information will already be lost.

Have you done so?

Re: Qlikview converting Text to Number and removing leading zeros

Wed, 16 Oct 2013 08:16:07 GMT

Datasource in our case is Oracle. Did try TO_CHAR function (equivalent of Text) while selecting from Oracle, just to insure that everything is treated as Character string, didn't work.

Qlikview while parsing data overrides the fact that data selected is string. It is treating it as Number with Decimal value regardless of me using TO_CHAR or Text later on.

As for data, when I do pivot on those two values, there is data associated for in QVD, as other columns are visible.

So we are stuck for now with Derived Column - to force Qlikview to think they are two separate Strings.

This sounds like major drawback where it could lead to serious issues with data when dealing with billions of rows.

Regards

Ravi

Re: Qlikview converting Text to Number and removing leading zeros

swuehl — Wed, 16 Oct 2013 08:22:21 GMT

The DB function TO_CHAR won't help, because QV will try to interpret your value as a number unless text() function in a LOAD is used, like

LOAD

Text(FIELD) as FIELD;

SQL SELECT

FIELD

FROM DBTABLE;

Re: Qlikview converting Text to Number and removing leading zeros

MayilVahanan — Wed, 16 Oct 2013 08:26:02 GMT

Try like this

LOAD

...

Text(Account_type) as Account_type;

SQL SELECT

Account_type,

...

FROM DBTABLE;

Re: Qlikview converting Text to Number and removing leading zeros

Wed, 16 Oct 2013 09:04:28 GMT

Thanks everyone.

Have to say this was not straight forward and not straight way to handle it. Most of standard tools do keep in mind data type while selecting.

Thanks

Regards

Ravi

Re: Re: Qlikview converting Text to Number and removing leading zeros

swuehl — Wed, 16 Oct 2013 09:09:42 GMT

Agreed that this might not be intuitive.

Please check these blog posts:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/13/dual

http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/07/automatic-number-interpretation