topic Re: Concatenate in the sql select in the script of qlikview from SAP CAR table in QlikView

Concatenate in the sql select in the script of qlikview from SAP CAR table

geogou1973 — Thu, 08 Jul 2021 12:23:59 GMT

Hello,

I have the following script

select BUSINESSDAYDATE
RETAILSTOREID
RETAILTYPECODE
TRANSINDEX
SUM(REDUCTIONAMOUNT)
from /POSDW/TLOGF
where businessdaydate > '20210101' and retailtypecode <> ' ' AND CONCAT(TASK_CANCELED, CHANGEFLAG) <> 'X '
GROUP BY BUSINESSDAYDATE
RETAILSTOREID
RETAILTYPECODE
TRANSINDEX;

from a table of SAP CAR. In the where i want to concatenate two fields in order to have a combination of X and blank.

I give the expression CONCAT(TASK_CANCELED, CHANGEFLAG) , i give the expression TASK_CANCELED || CHANGEFLAG, i give the expression TASK_CANCELED && CHANGEFLAG and the script fails.

Have someone an idea how can i write this in order to have the result i want ;

Thank you in avdanced.

Re: Concatenate in the sql select in the script of qlikview from SAP CAR table

PiEye — Fri, 09 Jul 2021 08:58:36 GMT

Hi GeoGou

Are you hoping to solve this in SAP or in Qlik?

In Qlik, it is fairly simple - you can achieve this with an '&'. Simplest way to apply this to a SQL load is to precede it with a load statement and put the where clause there:

LOAD

WHERE TASK_CANCELED & CHANGEFLAG 'X '

;

select

TASK_CANCELED

CHANGEFLAG

BUSINESSDAYDATE
RETAILSTOREID
RETAILTYPECODE.....

However I appreciate that the SQL is limited for a reason. It looks like a SAP issue, rather than Qlik. What error message are you getting?

See if you can check first the Data Types of CHANGEFLAG and TASK_CANCELED , if they are different, or numeric then this will not work.

Also, you are checking that the concatenation of them both <>'X'. Since this is one character, then logically you could also test for ( CHANGEFLAG <> 'X AND TASK_CANCELED <> 'X') .

Hope these suggestions help

Re: Concatenate in the sql select in the script of qlikview from SAP CAR table

PiEye — Fri, 09 Jul 2021 08:59:37 GMT

Apologies, the above preceeding load statement should read <> 'X':

LOAD

WHERE TASK_CANCELED & CHANGEFLAG <> 'X '

;

Re: Concatenate in the sql select in the script of qlikview from SAP CAR table

geogou1973 — Mon, 12 Jul 2021 17:24:11 GMT

Hello PiEye,

I know how to concatenate in the script of load expression in qlikview.

I want to do this in the sap select sql with the sap connector in order to avoid the large number of data.

Have anyone an idea how can i do this ?

Re: Concatenate in the sql select in the script of qlikview from SAP CAR table

PiEye — Tue, 13 Jul 2021 06:30:42 GMT

Hi GeoGou, I did offer some other suggestions, not just Qlik.

As above, either concat(..,....) or || should work. we need more information to troubleshoot why this isn't working.

It could be many things - maybe one of the strings are nulll, or maybe it is not a string. can you check the data types?

Are you are getting an error message? This would help pinpoint the issue.