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

[sunny_talwar]

When you just run this

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)' ;

do you see a field name called BUKRS within your BSAS table? May be the casing is different?

[aarkay29]

You were missing  a closing ')' at the end that might be one of the the reason.

[BSAS]:

Load

BUKRS;

SQL SUBSELECT *

FROM BSAS

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

[Anonymous]

Dear Sunny,

I am able to load BSAS table which is having BUKRS field.

[Anonymous]

Dear Aar,

I also put closing bracket at the end but it is still showing same error.

[aarkay29]

Harinder

Everything seems to run perfectly when I try  the same code on Qlikview.

Can you make sure that $(vFromDate_BKPF) is filled with appropriate date in YYYYMMDD format

[Anonymous]

I have checked the date format which is working fine.

when i am using only

SQL SUBSELECT *

FROM BSAS

where BELNR GJAHR BUKRS IN ( SELECT BELNR GJAHR BUKRS FROM BKPF where CPUDT >= '$(vFromDate_BKPF)') ; syntax it is working fine. but i am not able to load the data.

I am using Qlik Sense & I don't think that will be the problem.

[sunny_talwar]

Would you be able to share a screenshot of the error you are seeing?

[Anonymous]

PFA screenshot of error

[sunny_talwar]

Not sure I know. I mean if your table BSAS contains BUKRS then there is no reason for it show that error. But you did confirm that BUKRS was a field when you just ran this, right?

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)' ;

If that is the case, then I am not sure why it would error out. May be someone else have other ideas