Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
You can use SUBFIELD
SUBFIELD([Account Type],',',) as [Account Type]
Regards
Alan
Should the Inline Statement not read like this
Try
Load
text(Account_Type) as Account_Type
Inline [
Account_Type
'20.001',
'20.00100',
'20.0010'];
That works swuehl,
I was getting myself confused, I though it was a simple answer and it was
Thanks
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
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
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?
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
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;