topic Re: Concat function in Direct Discovery in QlikView

Concat function in Direct Discovery

sindu_bharathi — Mon, 13 Oct 2014 07:46:51 GMT

Hi,

I am developing an application with direct discovery. Tried to create a Composite key with concat function on the direct discovery table. Getting undefined expression error message while reloading.

Does Direct Discovery supports concat function?

Regards,

Sindu

Re: Concat function in Direct Discovery

Anonymous — Fri, 13 Feb 2015 15:47:19 GMT

Hi Sinduja,

I think that you can't use Qlik functions inside Direct Discovery.

I don't know if this would help you: field1 + '|' + field2 as CompositeKey, where + it's a substitute of &.

Hope it help!

Re: Concat function in Direct Discovery

agomes1971 — Fri, 13 Feb 2015 17:16:48 GMT

Hi,

when use concatenations use native(field1 + 'I' + field2) I think...

Regards

André Gomes

Re: Concat function in Direct Discovery

Anonymous — Fri, 13 Feb 2015 17:41:07 GMT

Hi Andre,

I tried without NATIVE statement and works fine for me. This is how I did it:

DIRECT QUERY

DIMENSION

Customer,

Shop,

Vendedor,

Customer + '|' + Shop as KeyCustomerShop,

NATIVE('CAST (EAN as nvarchar(30))') as EAN

MEASURE

UDS,

Amount

FROM dbo.Sales

Probably your way is better than mine. Anyway it's just antoher way to do it.

Regards,

Jordi González

Re: Concat function in Direct Discovery

sindu_bharathi — Thu, 19 Feb 2015 05:51:42 GMT

Hi Jordi,

I tried using + for concatenation ,but still i m getting error .

Direct Query

Dimension

CustomerID,

CustomerID+'|'+SalesOrderID as CompositeKey,

Orderdate

Measure

SubTotal,

TaxAmount

From Sales_SalesOrderHeader;

Though we cant use most of the Qlik functions inside DD,we could use some of them. Qlikview help doc says we can use concat function in DD. but when i tried using concat its not working.

DIRECT QUERY DIMENSION concat(region, 'code') AS region_code MEASURE Num1 AS NumA FROM TableName

Regards,

Sindu

Re: Concat function in Direct Discovery

Anonymous — Thu, 19 Feb 2015 11:08:00 GMT

Hi Sinduja,

What is the description of the error? I've tried in my doc example and says something like "Conversion failed when converting the varchar value '|' to data type int."

Seems that you can't use the sentence "field1 + '|' + field2" if the fields aren't a char/varchar. So, you must use agomes1971 NATIVE example to cast the fields.

Try this, it worked for me (CustomerID and SalesOrderID are Integers):

Direct Query

Dimension

CustomerID,

NATIVE ('CAST (CustomerID as varchar(100)) +CHAR(124)+ CAST (SalesOrderID as varchar(100))') as CompositeKey,

Orderdate

Measure

SubTotal,

TaxAmount

From Sales_SalesOrderHeader;

If you got a Char and a Integer you only have to CAST the Int.

Hope it helps.

Regards,

Jordi González