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

[geogou1973]

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.

[PiEye]

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

Pi

[PiEye]

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

LOAD  

*

WHERE TASK_CANCELED & CHANGEFLAG <> 'X '

;

[geogou1973]

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 ?

[PiEye]

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.

Pi