Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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
Master II
Master II

You can use SUBFIELD

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

Regards

Alan

rustyfishbones
Master II
Master II

Should the Inline Statement not read like this

2013-10-15_2243.png

swuehl
MVP
MVP

Try

Load

     text(Account_Type) as Account_Type

Inline [

Account_Type

'20.001',

'20.00100',

'20.0010'];

rustyfishbones
Master II
Master II

That works swuehl,

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

Thanks

Not applicable
Author

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
Author

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

swuehl
MVP
MVP

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
Author

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

swuehl
MVP
MVP

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;