topic Re: SUBSELECT & Load statement is giving error in QlikView

SUBSELECT & Load statement is giving error

Anonymous — Fri, 24 Feb 2017 11:33:33 GMT

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

Re: SUBSELECT & Load statement is giving error

sunny_talwar — Fri, 24 Feb 2017 12:03:49 GMT

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?

Re: SUBSELECT & Load statement is giving error

aarkay29 — Fri, 24 Feb 2017 12:47:19 GMT

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

Re: SUBSELECT & Load statement is giving error

Anonymous — Sat, 25 Feb 2017 14:53:43 GMT

Dear Sunny,

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

Re: SUBSELECT & Load statement is giving error

Anonymous — Sat, 25 Feb 2017 14:55:36 GMT

Dear Aar,

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

Re: SUBSELECT & Load statement is giving error

aarkay29 — Sat, 25 Feb 2017 16:52:42 GMT

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

Re: SUBSELECT & Load statement is giving error

Anonymous — Sat, 25 Feb 2017 17:37:16 GMT

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.

Re: SUBSELECT & Load statement is giving error

sunny_talwar — Sat, 25 Feb 2017 17:38:33 GMT

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

Re: SUBSELECT & Load statement is giving error

Anonymous — Sat, 25 Feb 2017 17:44:32 GMT

PFA screenshot of error

Re: SUBSELECT & Load statement is giving error

sunny_talwar — Sun, 26 Feb 2017 16:24:55 GMT

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

Re: SUBSELECT & Load statement is giving error

aarkay29 — Sun, 26 Feb 2017 18:42:07 GMT

Harinder,

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

Re: SUBSELECT & Load statement is giving error

Anonymous — Mon, 27 Feb 2017 04:38:58 GMT

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;

Re: SUBSELECT & Load statement is giving error

Peter_Cammaert — Mon, 27 Feb 2017 07:46:27 GMT

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.

Re: SUBSELECT & Load statement is giving error

Peter_Cammaert — Mon, 27 Feb 2017 07:50:18 GMT

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.

Re: SUBSELECT & Load statement is giving error

aarkay29 — Mon, 27 Feb 2017 14:25:50 GMT

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