topic Re: Concatenate fields in SQL Statement in QlikView in App Development

Concatenate fields in SQL Statement in QlikView

NicolasRivas — Sat, 16 Nov 2024 16:34:59 GMT

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.

Re: Concatenate fields in SQL Statement in QlikView

Qrishna — Wed, 23 Oct 2024 17:08:26 GMT

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.

Re: Concatenate fields in SQL Statement in QlikView

NicolasRivas — Wed, 23 Oct 2024 16:59:27 GMT

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.

Re: Concatenate fields in SQL Statement in QlikView

Qrishna — Wed, 23 Oct 2024 17:30:07 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

NicolasRivas — Wed, 23 Oct 2024 17:14:25 GMT

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'

Re: Concatenate fields in SQL Statement in QlikView

NicolasRivas — Wed, 23 Oct 2024 17:59:50 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

Kushal_Chawda — Wed, 23 Oct 2024 18:24:28 GMT

@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;

Re: Concatenate fields in SQL Statement in QlikView

NicolasRivas — Wed, 23 Oct 2024 18:38:07 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

Qrishna — Wed, 23 Oct 2024 19:10:17 GMT

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)

Re: Concatenate fields in SQL Statement in QlikView

stevedark — Wed, 23 Oct 2024 19:28:25 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

Kushal_Chawda — Wed, 23 Oct 2024 19:29:05 GMT

@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))

Re: Concatenate fields in SQL Statement in QlikView

stevedark — Thu, 24 Oct 2024 12:58:53 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

NicolasRivas — Thu, 24 Oct 2024 13:51:03 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

NicolasRivas — Thu, 24 Oct 2024 13:56:21 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

stevedark — Thu, 24 Oct 2024 14:00:34 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

stevedark — Thu, 24 Oct 2024 14:07:43 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

NicolasRivas — Thu, 24 Oct 2024 14:39:29 GMT

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

Re: Concatenate fields in SQL Statement in QlikView

stevedark — Thu, 24 Oct 2024 14:45:10 GMT

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.

Re: Concatenate fields in SQL Statement in QlikView

Kushal_Chawda — Thu, 24 Oct 2024 14:51:32 GMT

@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))

Re: Concatenate fields in SQL Statement in QlikView

NicolasRivas — Thu, 31 Oct 2024 13:22:09 GMT

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.