topic Re: SQL SELECT Concat issue in QlikView

SQL SELECT Concat issue

rustyfishbones — Thu, 16 Oct 2014 14:55:33 GMT

Hi All,

I have the following piece of code for extracting from the DB and it works ok

However it does not concat the "trx-no" within the string and I get no error message

Any idea why I can't join the trx-no to the rest of the string?

Regards

Alan

Re: SQL SELECT Concat issue

alexandros17 — Thu, 16 Oct 2014 14:57:23 GMT

are you sure that trx-no is not null or empty?

Re: SQL SELECT Concat issue

flipside — Thu, 16 Oct 2014 14:58:35 GMT

I was about to suggest bringing throught that field (with CAST statement) on its own to see if indeed that was the situation.

Re: SQL SELECT Concat issue

Anonymous — Thu, 16 Oct 2014 14:59:48 GMT

Try replacing the double quotes in your SQL statement with open and close square brackets. The SQL engine could be interpreting your double quotes as string values rather than column names.

Also, try copying and pasting everything after the SQL keyword into SQL Server Management Studio and running directly against the DB. Better error feedback that way.

Re: SQL SELECT Concat issue

rustyfishbones — Thu, 16 Oct 2014 15:03:09 GMT

Yes I am sure

trx-no is always available, no nulls or empty data

Re: SQL SELECT Concat issue

rustyfishbones — Thu, 16 Oct 2014 15:04:34 GMT

Hi Steve,

I tried that but no luck I'm afraid

Re: SQL SELECT Concat issue

rustyfishbones — Thu, 16 Oct 2014 15:07:06 GMT

Hi flipside

trx-no loads no problem with all values, however when I change to

CAST("trx-no" AS CHAR) AS trx-no

All values are blank??

maybe I'll try CONVERT

Re: SQL SELECT Concat issue

Anonymous — Thu, 16 Oct 2014 15:07:33 GMT

Try casting it as a varchar instead of char and supply an argument that is matches the max expected length of the field.

even if you use varchar(20) and it only takes up 3 digits, it will drop the extras. I think it may be the lack of length argument that is causing your problems.

Re: SQL SELECT Concat issue

alexandros17 — Thu, 16 Oct 2014 15:09:41 GMT

... what about your query if you adda anew column trx-no?

Is it visible?

There is anoher solution:

Do the same with Qlik so:

Load ..;

Select

Re: SQL SELECT Concat issue

flipside — Thu, 16 Oct 2014 15:14:03 GMT

Can you bring it through without the CAST and make the concatenation in a preceding load?

flipside

EDIT: As Alessandro says !

Re: SQL SELECT Concat issue

rustyfishbones — Thu, 16 Oct 2014 15:17:45 GMT

Excellent that fixes the issue

Thanks Steve

Regards

Alan

Re: SQL SELECT Concat issue

rustyfishbones — Thu, 16 Oct 2014 15:22:01 GMT

final result is this

warehouse +

customer +

product +

"trx-by" +

CAST("trx-no" AS VARCHAR(20)) +

CAST("item-number" AS CHAR) +

CAST("line-number" AS CHAR) AS TEST