Concatenate fields in SQL Statement in QlikView

[NicolasRivas]

Hello,

I have do a sql query to a sap table named VBUP, but i have to select filter the rows that doesn't already exists in a QVD I have stored of that same table. I am trying a lot of ways but I am not able to concatenate the fields in the sql statement to compare it to the values of the QVD. this is what I am trying now:

EXISTENTES:
FIRST 10 LOAD
	%KEY_VBUP_MANDT_POSNR_VBELN
FROM
[..\QVD\DATA_SAP_01_AR_400_VBUP.QVD]
(qvd);

EXISTENTES_SIN_CHAR:
NOCONCATENATE LOAD
	CONCAT(PURGECHAR(%KEY_VBUP_MANDT_POSNR_VBELN,'|'),',')	AS %KEY_VBUP_MANDT_POSNR_VBELN
//	PURGECHAR(%KEY_VBUP_MANDT_POSNR_VBELN,'|')				AS %KEY_VBUP_MANDT_POSNR_VBELN
RESIDENT EXISTENTES;
DROP TABLE EXISTENTES;

LET vFilterValues = PEEK('%KEY_VBUP_MANDT_POSNR_VBELN',0,'EXISTENTES_SIN_CHAR'); // You need to generate this list from QlikView


$(S_NmTabla) :   // Documento de ventas: Datos de cabecera
LOAD 
  MANDT, 
  POSNR,
  VBELN;
SQL Select
	MANDT 
	POSNR 
	VBELN
FROM VBUP
WHERE (MANDT || POSNR || VBELN) IN ($(vFilterValues))
;

I already tried with + or & or || or with concat(mandt,posnr,vbeln) but i always get some error,

I have to fitler it in the sql because I dont want to retrieve ALL the rows so I can lower the execution time.

[Kushal_Chawda]

@NicolasRivas  If concatenation field doesn't support in SAP query of where clause, you may need to look for other alternatives. One option is you could try below sub query

SQL Select a.* from 
(
Select MANDT,
       POSNR,
       VBELN,
       MANDT || POSNR || VBELN as KEY
FROM VBUP) AS a
where a.KEY in ($(vFilterValues))

[stevedark]

Hi @NicolasRivas 

Just realised, following up on my post yesterday. Unless the QVD you are checking for existing values against has each combination of values only once, you will want to add a DISTINCT to the CONCAT statement or you could have a massive list of values:

CONCAT(DISTINCT chr(39) & PURGECHAR(%KEY_VBUP_MANDT_POSNR_VBELN,'|') & chr(39),',')

Hope that helps,

Steve

[NicolasRivas]

hello @Kushal_Chawda 

Thank you for you advice!

I tried it but it doesn't work either, its showing me this error

I trie laso with concat(), with subselect, wit posnr and vbeln instead of a.*, but its always showing me that error

[NicolasRivas]

hello @stevedark 

first of all thank you for your advice,

I think the filter variable is okay because I am seeing the data in the correct format in the debug, as you can see here 

WHERE MANDT + POSNR + VBELN NOT IN ($(vFilterValues))

---

Also I tried this, also tried, posnr || vbeln, concat(posnr,vbeln), posnr & vbeln, posnr & vbeln, but nothing works, its always showing me this error

[stevedark]

Hi @NicolasRivas 

The issue here is that different flavours of SQL have different syntax. The syntax @Kushal_Chawda has given for a subquery looks spot on, but it may not work with the particular SQL you are using.

Try building up the statement, without using variables, in a separate app to find where it breaks.

For instance, try it first without the WHERE statement, to see if the problem is there.

If the concatenation code is correct I don't see any reason why it would work in a subquery but not in the WHERE statement though.

Steve

[stevedark]

I think the first issue you need to crack is simply one of string concatenation. Try testing without any where statement or anything else, just a simple:

SQL Select
	POSNR || VBELN as KEY
FROM VBUP

Until that is working nothing else matters.

I see from one of the table names that SAP is involved. In which case it seems || is correct, but suggest also trying double ampersand && in their place.

Doing this in the SQL query analyser, not in Qlik at all, may make it quicker to find the solution.

Steve

[NicolasRivas]

Yes, I already tried doing a simple query like the one you are mentioning but it doesnt work either, the same error is showing.

[stevedark]

Do you know what type of database it is you are loading from? Without getting the simple concatenate in a load statement working there is no way of getting the rest to work.

[Kushal_Chawda]

@NicolasRivas  It seems this type of sub query is not supported in SAP. You could try with CTE

SQL 
WITH VBUPTemp
  AS ( SELECT POSNR,
              VBELN,
	      POSNR || VBELN as KEY 
       FROM VBUP 
      )
SELECT POSNR,VBELN,KEY
FROM VBUPTemp
where KEY in ($(vFilterValues))

[NicolasRivas · Accepted solution]

Thank you all for your advices,

I change the logic to use another sap table so I could fitler by a single field, and that did it.

Thank you.