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.

[Qrishna]

Try below:

// Load all tables and their fields dynamically
FOR i = 0 TO NoOfTables()-1

LET vTable = TableName(i); // Get the name of the table

FOR j = 1 TO NoOfFields(vTable) // Loop through each field in the table

LET vField = FieldName(j, vTable); // Get the name of the field

// Load each table name and field name into a new table
TableFieldInfo:
LOAD
'$(vTable)' as TableName,
'$(vField)' as FieldName
AUTOGENERATE 1;
// from Your_Table_name - this works if you have a specif table to work with, if working with this remove the outer for loop FOR i = 0 TO NoOfTables()-1

NEXT j

NEXT i

// Create a new table that will store concatenated values
ConcatenatedValuesTable:
LOAD
Concat(DISTINCT FieldName, ', ') as ConcatenatedFieldValues
RESIDENT TableFieldInfo;

LET vFilterValues = PEEK('ConcatenatedFieldValues',0,'ConcatenatedValuesTable');

Just note if you wanna compare fields from the SQL table and existing Qlik Table, my preferred method would be by using where not exist(), after i have fieldname info from both the tables.

[NicolasRivas]

Hello Qrishna,

Thank you for your quick response, But i don't think this is a solution here, because what I have to do is take the existing rows of the VBUP table that I have in the vbup.qvd and do a "where not exists(rowsin the qvd, rows in sql)" but i have to do it IN the SQL statement.

I am having trouble concatenating the fields to make the key that I have to make to compare it with the key in the QVD.

[Qrishna]

you mean here in in this pice of code: 

WHERE (MANDT || POSNR || VBELN) IN ($(vFilterValues))

when  you said '' but i have to select the fields that doesnt already exists in a QVD", thas what the above code is for.

I believe you are looking for incremental load logic, as you dont want to load all the rows that already exist in QLikl QVD, but load only non existing rows. 

try below:

EXISTENTES:

FIRST 10 LOAD

KEY_VBUP_MANDT_POSNR_VBELN

FROM

[..\QVD\DATA_SAP_01_AR_400_VBUP.QVD]

(qvd);

// Store unique field values into variables to use in SQL WHERE clause

LET vKEY_VBUP = Concat(DISTINCT chr(39) & KEY_VBUP_MANDT_POSNR_VBELN & chr(39), ', ');  // Concatenate ID values with quotes

// Load SQL table with a WHERE clause that excludes the KEY_VBUP_MANDT_POSNR_VBELN already present in QlikTable

SQLTable:

SQL SELECT

         MANDT 

         POSNR 

         VBELN

FROM YourSQLTable

WHERE MANDT NOT IN ($(vKEY_VBUP))

              (AND/OR) POSNR NOT IN ($(vKEY_VBUP)) ....

;  // Exclude rows with $(vKEY_VBUP) already in QlikTablele

[NicolasRivas]

exactly, i am doing there 

MANDT || POSNR || VBELN

 jn an attempt to concatenate the ifled to makye the key I want to compare. But that throws me the error  '"|" is not a valid comparison operator'

[NicolasRivas]

I tried it bbut the problem with this code is that for example, lets suppose i have in the qvd only the VBELN 1  with POSNR 10 and VBELN 2 with POSNR 30, and in sap i have VBELN 1 with POSNR 30, this code will not add it to my qvd because the VBELN 1 exists and the POSNR 30 exists. I need to compare the key entirely

[Kushal_Chawda]

@NicolasRivas  I doubt that concat() function will work for SAP but give it a try.

WHERE concat(MANDT ,'|',POSNR,'|', VBELN) IN ($(vFilterValues))

If concatenation of fields is not possible in where clause of SAP you can perform inner join between QVD & SAP table;

[NicolasRivas]

Hello @Kushal_Chawda , I tried it but it doesn't work, and the thing about the inner join, is that in order to do that, i have to retrieve all rows rom SAP table i dont want to retrieve all of them to lower the excution time

[Qrishna]

im not really sure if you could use concatenation with ||  to create a composite condition in the IN clause like that as its a logical operator.  we usually do soemthing like below:

WHERE NOT (MANDT IN ($(vKEY_VBUP)) OR POSNR IN ($(vKEY_VBUP)) OR VBELNIN ($(vKEY_VBUP)))

WHERE NOT (MANDT IN ($(vKEY_VBUP)) || POSNR IN ($(vKEY_VBUP)) || VBELN IN ($(vKEY_VBUP)))

you can give atry to this too:

WHERE MANDT NOT IN (SELECT MANDT FROM QlikTable) AND POSNR NOT IN (SELECT POSNR FROM QlikTable)

[stevedark]

Hi @NicolasRivas 

The SQL statement can not see anything within your Qlik script or data model, as it is executed on the SAP server.

The only way you will be able to achieve this is to craft the SQL server by concatenating strings into a variable.

You are very much headed in the right direction with your code, but you need to ensure what you come out with at the end is a valid SQL statement.

To ensure that the vFilterValues variable contains what you think it does add this line after you set the variable:

TRACE $(vFilterValues);

It looks to me like you will get a comma separated set of values, without quotes. Is that what you are after?

I think the concat probably wants to be:

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

This will add a single quote around each value.

That variable will be inserted into your code exactly as it appears in the trace. You can therefore work out what SQL statement you are sending to the server.

It seems to me that the issue is with the SQL code, so you could try just running the SQL in the query analyser in your database - this will be quicker to debug.

All SQL variants differ a little, so Google how to do a string concatenate in the one you are using (+ is quite common).

Once you have the concatenation syntax, I think you probably want a SQL statement similar to this, to exclude values rather than include them:

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

But, as I say it will depend on your flavour of SQL.

Hope that helps.

Steve