topic Re: Re: Loading Column that has a $ sign in QlikView

Loading Column that has a $ sign

Mon, 17 Nov 2014 23:07:53 GMT

Hello,

I am trying to load a table from the database and one of the columns has a dollar sign something like "TotalSales$K". qlikView fails to read this column because of the dollar sign. Does anyone know how I can load this column without changing the database?

thanks,

Manoj Agrawal

Re: Loading Column that has a $ sign

Tue, 18 Nov 2014 01:10:07 GMT

Thanks for the reply but since $ is a system field QlikView ignores it and returns the error "field [TotalSales] not found".

regards,

Manoj

Re: Loading Column that has a $ sign

SergeyMak — Tue, 18 Nov 2014 01:34:44 GMT

Hi blackmon,

Could you show the actual script.

I've just tried do load data from excel with $-sign in the field. It looks ok.

Regards,

Sergey

Re: Loading Column that has a $ sign

Tue, 18 Nov 2014 01:51:08 GMT

Hi Sergey,

My bad. The field is "TotalSales$(K)" and it errors out.

thanks,

Manoj

Re: Re: Loading Column that has a $ sign

SergeyMak — Tue, 18 Nov 2014 01:55:04 GMT

Manoj,

could you show the actual script?

Regards,

Sergey

Re: Loading Column that has a $ sign

SergeyMak — Tue, 18 Nov 2014 01:58:45 GMT

I just tested it with MS Access

Try to use `sign instead of [] for the filed name

The following script is working:

OLEDB CONNECT TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=Z:\data\Test\QlikCommunity\2014.11.18\test.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False];
LOAD ID,
     `test$a`,
     testB;
SQL SELECT ID,
     `test$a`,
     testB
FROM Table1;

Re: Loading Column that has a $ sign

Tue, 18 Nov 2014 02:05:30 GMT

Hi Sergey,

Please try with test$(a).

regards,

Manoj

Re: Loading Column that has a $ sign

SergeyMak — Tue, 18 Nov 2014 02:19:09 GMT

Could you just rename the column in Access DB?

Regards,

Sergey

Re: Loading Column that has a $ sign

jagan — Tue, 18 Nov 2014 03:15:56 GMT

Hi Manoj,

Did you tried this by using the Select button in Edit Script window? Try to generate the query using Select button and check.

OR try this

SQL SELECT ID,
"test$a" AS ColumnName,
testB
FROM Table1;

Re: Re: Loading Column that has a $ sign

SergeyMak — Tue, 18 Nov 2014 03:21:47 GMT

Hi Jagan,

The problem is not in $-sign but in $(a) at the end. It's interpreting as $-sign expansion

Regards,

Sergey

Re: Re: Loading Column that has a $ sign

jagan — Tue, 18 Nov 2014 03:32:44 GMT

Hi,

Just a try if we do this using Qlikview's Select button may be they would have handled, not sure about this. Lets see how they handled this.

We can try creating a view in database and give alias to the column name in view and use this view to access the data in qlikview.

Hope this helps.

Regards,

Jagan.

Re: Re: Loading Column that has a $ sign

Tue, 18 Nov 2014 06:56:39 GMT

Jagan,

doesn't make a difference whether you write the query yourself or use the select button.

Obviously it is simple to edit the column name in the database but I was just wondering if there was a way to write characters just like &amp in HTML.

thanks,

Manoj

Re: Re: Loading Column that has a $ sign

SergeyMak — Tue, 18 Nov 2014 07:02:54 GMT

Hi Manoj,

I tried to use chr(36) in different ways, but it doesn't work, unfortunately.

I see only one way - build a view in DB where you can rename this field and use this view as a source.

Regards,

Sergey

Re: Loading Column that has a $ sign

jyothish8807 — Tue, 18 Nov 2014 07:05:13 GMT

Hi Manoj,

I check this and observed that QV ignores the $(a) part.

Eg. if field name id amount$(a) on reload it takes Amount as field name.But i dosent give error.

Regards

Re: Re: Loading Column that has a $ sign

jagan — Tue, 18 Nov 2014 07:05:51 GMT

Hi,

As I suggested earlier you can create view on top of the table, so that you don't need to alter the table.

Regards,

Jagan.

Re: Loading Column that has a $ sign

anbu1984 — Tue, 18 Nov 2014 07:27:16 GMT

Use Load * From Table;

Re: Loading Column that has a $ sign

Tue, 18 Nov 2014 15:59:12 GMT

Hi Anbu,

Yes, I guess that is one way but then I would be loading all the other columns that are not needed.

thanks,

Manoj

Re: Loading Column that has a $ sign

Tue, 18 Nov 2014 16:02:17 GMT

put condition like

where wldmatch(*$*) ....

Re: Loading Column that has a $ sign

Tue, 18 Nov 2014 16:20:42 GMT

I did not get your answer. Can you write the script say for a table that has one column TotalSales$(K)?

thanks,

Manoj

Re: Loading Column that has a $ sign

Tue, 18 Nov 2014 16:24:05 GMT

i am not sure it works

my idea is

load

from table

where wildmatch(*$*);