topic SQL SELECT with - Where condition from Resident Table in QlikView

SQL SELECT with - Where condition from Resident Table

girish_talele — Wed, 03 Dec 2014 13:09:23 GMT

Dear Experts,

Below given condition will work in resident table but

Is there any way to map below requirement in SQL SELECT from SAP database.

//===============================================================

Emp:

Load * INLINE [

Emp, Ind

30000000, Yes

30000001, Yes

];

//===============================================================

SAP_Data:

LOAD

PERNR AS Employee,

NACHN AS Surname;

SQL SELECT PERNR NACHN FROM PA0002 Where ApplyMap('Emp', PERNR,'No') = 'Yes';

//===============================================================

Re: SQL SELECT with - Where condition from Resident Table

yduval75 — Wed, 03 Dec 2014 13:17:58 GMT

ApplyMap is a Qlikview Function. It must operate ine the Qlikview instruction.

Moreover you have to specify Mapping Load in your Map instruction

Emp:

Mapping

Load * INLINE [

Emp, Ind

30000000, Yes

30000001, Yes

];

SAP_Data:

LOAD

PERNR AS Employee,

NACHN AS Surname

Where ApplyMap('Emp', PERNR,'No') = 'Yes'

;

SQL SELECT

PERNR NACHN

FROM PA0002;

Re: SQL SELECT with - Where condition from Resident Table

anbu1984 — Wed, 03 Dec 2014 13:20:26 GMT

You cannot use Qlikview function in Sql. Either you have to hardcode in Sql or you can use ApplyMap() on Preceding Load

LOAD

PERNR AS Employee,

NACHN AS Surname Where ApplyMap('Emp', PERNR,'No') = 'Yes';

SQL SELECT PERNR NACHN FROM PA0002 ;

Re: SQL SELECT with - Where condition from Resident Table

Aurelien_Martinez — Wed, 03 Dec 2014 14:27:14 GMT

Hi,

You can try :

Emp:

Load * INLINE [

Emp, Ind

30000000, Yes

30000001, Yes

];

SUPERTEST:

LOAD

Concat(Emp, ',') as TEST

Resident Emp

Where Ind='Yes'

;

LET vTest = Peek('TEST', 0, 'SUPERTEST');

SAP_Data:

LOAD

PERNR AS Employee,

NACHN AS Surname;

SQL

SELECT PERNR NACHN

FROM PA0002

WHERE PERNR in ($(vTest))

;

drop table SUPERTEST;

Re: SQL SELECT with - Where condition from Resident Table

girish_talele — Fri, 05 Dec 2014 05:08:43 GMT

Dear Aurélien MARTINEZ,

Thanks, it works (provided the no. of values in concat are few).

Regards,

Girish.

Re: SQL SELECT with - Where condition from Resident Table

sidjustice — Fri, 29 Apr 2016 18:27:04 GMT

Any idea what is the limit on the number of values in concat?