SUBSELECT & Load statement is giving error

[Anonymous]

Hi,

I hope you all are doing well.

I am using QlikSense & Qlik Sap SQL Connector,

In that I am using below SUBSELECT query for incremental load which is working fine.

Let vFromDate_BKPF = Date(Peek('MaxBKPF',0,'BKPFMAX'),'YYYYMMDD');

SQL SUBSELECT *

FROM BSAS

where BELNR GJAHR BUKRS IN ( SELECT BELNR GJAHR BUKRS FROM BKPF where CPUDT >= '$(vFromDate_BKPF)' ;

But when i am loading the data by using load statement i am getting an error -  field not found.

[BSAS]:

Load

BUKRS;

SQL SUBSELECT *

FROM BSAS

where BELNR GJAHR BUKRS IN ( SELECT BELNR GJAHR BUKRS FROM BKPF where CPUDT >= '$(vFromDate_BKPF)' ;

Thanks in advance

[aarkay29]

Harinder,

Not sure why it is giving you an error. If you don't mind can you please post your entire script.

[Anonymous]

Hi Aar,

PFB script

/************* Maxdate Logic ***************/

Temp:

LOAD CPUDT as Date

FROM [lib://Transaction_QVD_Path (tril_administrator)/BKPF.qvd]

(qvd);

BKPFMAX:

Load Max(Date) as MaxBKPF;

Load FieldValue('Date',IterNo()) as Date

autogenerate(1) 

while not IsNull(FieldValue('Date',Iterno()));

Let vFromDate_BKPF = Date(Peek('MaxBKPF',0,'BKPFMAX'),'YYYYMMDD');

DROP Table Temp,BKPFMAX;

/*************** Ends ****************/

//Let vStartTime=num(Now());

[BSAS]:

Load

BUKRS;

SQL SUBSELECT *

FROM BSAS

where BELNR GJAHR BUKRS IN ( SELECT BELNR GJAHR BUKRS FROM BKPF where CPUDT >= '$(vFromDate_BKPF)') ;

Exit Script;

[Peter_Cammaert]

I'm not entirely sure, but I don't think you need to use SUBSELECT when not reading from BSEG (

Cluster table). Try using a regular SELECT.

[Peter_Cammaert]

Are you sure the SQL statement returns at least one row? Sounds like an empty result set not creating an internal table, and therefor no BUKRS field.

[aarkay29]

Harinder,

It is very strange that you are getting an error!!!

When I try the same script everything seems to works fine

[BSAS]:

Load

BUKRS;

SQL SUBSELECT *

FROM BSAS

where BELNR GJAHR BUKRS IN ( SELECT BELNR GJAHR BUKRS FROM BKPF where CPUDT >= '20170101') ;