topic Re: Where clause while using SAP Extractor in QlikView

Where clause while using SAP Extractor

ahbzshk07 — Tue, 23 Feb 2016 08:56:35 GMT

Hello everyone!

I wanted to know the equivalent of IN( ) clause when we are loading in Qlikview by SQL SAP Extractor.

Should the condition be somewhat like:

SQL EXTRACTOR 80_EXTNAME

UPDMODE F // full extractor

EXTRLANGUAGE E

LOGSYS QTQVCEXTR1

WHERE

FieldName I CP '1,2,3';

I am not sure about the following part of CP.

Awaiting reply.

Thanks & Regards.

Re: Where clause while using SAP Extractor

Tomas_Sommergyll — Wed, 24 Feb 2016 10:11:36 GMT

Hi,

QV SAP Extractor Connector supports the following comparison operators in the WHERE clause:

EQ, NE, LT, GT, LE, GE, BT

However, I have successfully managed to run the (part of) script below using the 'CP' (Contains Pattern) to get 800 lines out of 160K:

WHERE

MATNR I CP '40*' ;

(Manually altered the script to get this, please see whole syntax of 'CP' at SAP)

BR,

Tomas

Re: Where clause while using SAP Extractor

Anonymous — Wed, 24 Feb 2016 10:19:01 GMT

In Qlik, you can try like this?

WHERE

FieldName>=1 and FieldName<=3;

WHERE

FieldName=1 or FieldName=2 or FieldName=3;

Re: Where clause while using SAP Extractor

ahbzshk07 — Wed, 24 Feb 2016 11:36:03 GMT

ohh, now I understood why CP is used for...

I actually wanted to put condition similar to this:

WHERE

FieldName IN ('1','2','3','L5');

So how will we do that when we are using QV SAP Extractor Connector?

Thanks & Regards.

Re: Where clause while using SAP Extractor

ahbzshk07 — Wed, 24 Feb 2016 11:36:43 GMT

Hello! I believe while using QV SAP Extractor Connector, syntax is different...

Thanks & Regards.

Re: Where clause while using SAP Extractor

Tomas_Sommergyll — Wed, 24 Feb 2016 15:36:51 GMT

IN is not supported but you can try using multiple lines, like this:

WHERE

FieldName I EQ '1',

FieldName I EQ '2',

FieldName I EQ '3',

FieldName I EQ 'L5'

;

BR,

Tomas

Re: Where clause while using SAP Extractor

ahbzshk07 — Wed, 24 Feb 2016 16:26:00 GMT

Yeah, no doubt, that is correct... I am looking for alternate solution...

I am sorry that I did not mention this before, but the value " '1','2','3','L5' ", I am storing in one variable in .qvs file. (as vValues = " '1','2','3','L5' ")

Now, considering this, how will the condition be?

I believe it is not
Where
FieldName I EQ $(vValues)

Thanks & Regards.

Re: Where clause while using SAP Extractor

Tomas_Sommergyll — Thu, 25 Feb 2016 10:20:36 GMT

Ok,

In that case, try experimenting with the following code:

// Use the appropriate input method of variables

// SET vValues='1','2','3','L5';

// $(Include=YourFile.qvs)

SET vFieldName ='FieldName I EQ ';

SET TmpStr='';

For each val in $(vValues)

TmpStr = '$(TmpStr)' & '$(vFieldName)' & Chr(39) & '$(val)' & Chr(39) & ', ';

LET Result = Left('$(TmpStr)',Len('$(TmpStr)')-2);

[Extractor Connector script goes here and Where clause as usual with the following variable]

WHERE

$(Result)

;