Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Qlikview converting Text to Number and removing leading zeros

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Qlikview converting Text to Number and removing leading zeros

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;

View solution in original post

12 Replies
rustyfishbones
Honored Contributor II

Re: Qlikview converting Text to Number and removing leading zeros

You can use SUBFIELD

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

Regards

Alan

rustyfishbones
Honored Contributor II

Re: Qlikview converting Text to Number and removing leading zeros

Should the Inline Statement not read like this

2013-10-15_2243.png

MVP
MVP

Re: Qlikview converting Text to Number and removing leading zeros

Try

Load

     text(Account_Type) as Account_Type

Inline [

Account_Type

'20.001',

'20.00100',

'20.0010'];

Highlighted
rustyfishbones
Honored Contributor II

Re: Qlikview converting Text to Number and removing leading zeros

That works swuehl,

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

Thanks

Not applicable

Re: Qlikview converting Text to Number and removing leading zeros

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

Not applicable

Re: Qlikview converting Text to Number and removing leading zeros

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

MVP
MVP

Re: Qlikview converting Text to Number and removing leading zeros

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?

Not applicable

Re: Qlikview converting Text to Number and removing leading zeros

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

MVP
MVP

Re: Qlikview converting Text to Number and removing leading zeros

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;

View solution in original post