topic SELECT as TEXT in QlikView

SELECT as TEXT

Fri, 03 Jul 2009 19:57:21 GMT

in one of our applications there is a SELECT from an account number. this is then paserd into level. App workfs fine but now we have a company that has accounts like '000002' and also a '2' consolidation account.

That 000002 consolidates into the '0' accounts.

Problem is that the 000002 is transformed by QV to 2. I've seen techniques that in a second table you can workarround this but ... only in a second derived table.

Is there a way to do this in 1 select, of then do a join of the original table to itself and then transform that field.

But all in the same table, other processed are doing quite some resident work on the original table.

Kind Regards

SELECT as TEXT

Fri, 03 Jul 2009 22:21:30 GMT

All fallen foul of this one!

In the load specify TEXT(ACCOUNTNUMBER) as ACCOUNTNUMBER so that Qlikview leaves it alone. Otherwise Qlikview sees the value of 000002 and identifies that as a number, so treats it the sames as 2.

Regards,

Gordon

SELECT as TEXT

Fri, 03 Jul 2009 23:24:23 GMT

In a LOAD ok, but in a SQL SELECT this doesn't work !!

SELECT as TEXT

Oleg_Troyansky — Fri, 03 Jul 2009 23:38:29 GMT

use a preceding load before your SQL Statement, it will apply any QlikView functionality on top of SQL Select:

load

...,

text(ACC) as [Account Number],

...

;

SQL SELECT ... , ACC, ... FROM mytable;

Oleg