topic Re: Script Logic in QlikView

Script Logic

tmumaw — Thu, 25 Jan 2018 13:16:53 GMT

Just wondering if this will work?

Thanks

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
WHERE Right([ZORDER],10) in (Select VBELN from VBAK)
;

[ZTRPST]: // Time Reports - Spread Time Posted
Load
[ZTICKNO]   as [Ticket],
Left([ZOBJECT],8) as EmpID,
Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],
Right([ZORDER],10) as [Contract],
[ZACTIVITY]   as [Contrac Line],
[ZSTATUS]   as [Status],
[ZWDATE]   as [Date],
ZHOURS   as [Z_Hours],
ceil(ZHOURS, .01) as [Hours],
round(ZHOURS, .01) as [Hours_Round],
floor(ZHOURS, .01) as [Hours_Floor]
Resident ZTRPSTTEMP
WHERE exists(Right([ZORDER],10), [VBELN])
;

Re: Script Logic

vishsaggi — Thu, 25 Jan 2018 14:19:42 GMT

You are already checking ZORDER values IN VBELN in your SQL Script. Why you want to check if exists in your resident load which you are loading from your SQL. Just trying to understand your requirement. IS VBELN coming from our SQL Script? Are you getting any errors when you run this resident load? Can you share some sample? Usually Exists will check if the data from 2nd table exists in the data from left table.

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 15:06:23 GMT

Here is the error message I get from the first step: Can I not do the Right command?

QVX_UNEXPECTED_END_OF_DATA: SQL_ERROR

Comma without preceding colon (after OPEN ?).

ZTRPSTTEMP]:

SQL Select *

FROM ZTRPST

WHERE Right([ZORDER],10) in (Select VBELN from VBAK)

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 15:14:35 GMT

Never mind my last error. I forgot the left bracket on my table name. DAH

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 16:29:01 GMT

Same message:

QVX_UNEXPECTED_END_OF_DATA: SQL_ERROR

Comma without preceding colon (after OPEN ?).

[ZTRPSTTEMP]:

SQL Select *

FROM ZTRPST

WHERE Right([ZORDER],10) in (Select VBELN from VBAK)

Re: Script Logic

vishsaggi — Thu, 25 Jan 2018 16:53:15 GMT

Does this script works in your SQL environment? Can you try and let us know.

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 16:56:28 GMT

I think I may have found it. The table I am trying to compare against has a contract ID as a length of 10 where the other has a length of 12. I changed the right function to a substr function giving it a starting position and length. We will see how this works. Thanks for your help I will keep you posted.

Re: Script Logic

vishsaggi — Thu, 25 Jan 2018 16:58:41 GMT

And also try to use VBELN in your select list if not that Field will not be loaded and used in your resident load.

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 17:59:55 GMT

Error: QVX_UNEXPECTED_END_OF_DATA: SQL_ERROR

2018-01-25 12:09:03 Comma without preceding colon (after OPEN ?).

2018-01-25 12:51:28 Execution Failed

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 18:00:58 GMT

Sorry forgot to add this:

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
WHERE substr([ZORDER],3,10) in (Select VBELN from VBAK)
;

[ZTRPST]: // Time Reports - Spread Time Posted
Load
[ZTICKNO]   as [Ticket],
Left([ZOBJECT],8) as EmpID,
Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],
Right([ZORDER],10) as [Contract],
[ZACTIVITY]   as [Contrac Line],
[ZSTATUS]   as [Status],
[ZWDATE]   as [Date],
ZHOURS   as [Z_Hours],
ceil(ZHOURS, .01) as [Hours],
round(ZHOURS, .01) as [Hours_Round],
floor(ZHOURS, .01) as [Hours_Floor]
Resident ZTRPSTTEMP
WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;

STORE * FROM [ZTRPSTTEMP] into QVD\ZTRPST.QVD
;
Exit Script;

Re: Script Logic

vishsaggi — Thu, 25 Jan 2018 18:24:00 GMT

This error does not need to be QV error. There is something we need to look into from SQL Side. Do you have a SQL connection before running your SQL Script inside QV.

I want to check few things here.

1. Will the SQL Script runs in your SQL editor?

2. Can you comment out the Resident Load script whole script i mean and just run your SQL script?

Please let me know.

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 19:05:28 GMT

I tried this and it works....

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
// WHERE substr([ZORDER],3,10) in (Select VBELN from VBAK)
WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;
exit SCRIPT;

Re: Script Logic

vishsaggi — Thu, 25 Jan 2018 19:48:13 GMT

Ok, does that work for you?

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 20:06:39 GMT

This works:

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;
exit SCRIPT;

I need this one to work:

[ZTRPSTTEMP]:
SQL Select *
FROM ZTRPST
WHERE substr([ZORDER],3,10) in (Select VBELN from VBAK)
;
exit SCRIPT;

Re: Script Logic

vishsaggi — Thu, 25 Jan 2018 20:13:45 GMT

May be you can try this? Change accordingly.

VBAK:

SQL

Select VBELN from VBAK

;

[ZTRPSTTEMP]:

SQL Select *, Right([ZORDER],10) AS ZorderID

FROM ZTRPST;

[ZTRPST]: // Time Reports - Spread Time Posted

Load

ZorderID,

[ZTICKNO] as [Ticket],

Left([ZOBJECT],8) as EmpID,

Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],

Right([ZORDER],10) as [Contract],

[ZACTIVITY] as [Contrac Line],

[ZSTATUS] as [Status],

[ZWDATE] as [Date],

ZHOURS as [Z_Hours],

ceil(ZHOURS, .01) as [Hours],

round(ZHOURS, .01) as [Hours_Round],

floor(ZHOURS, .01) as [Hours_Floor]

Resident ZTRPSTTEMP

WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and

(ZSTATUS = 'C' or ZSTATUS = 'I')

AND Exists(ZorderID, VBELN)

;

Drop Table [ZTRPSTTEMP];

STORE * FROM [ZTRPST] into QVD\ZTRPST.QVD

;

Exit Script;

Re: Script Logic

tmumaw — Thu, 25 Jan 2018 20:21:05 GMT

Here is my entire script. I have already extracted VBAK.

Re: Script Logic

vishsaggi — Thu, 25 Jan 2018 20:37:44 GMT

OK try this in your script and let me know if you getting any error:

VBAKTemp:

LOAD *

FROM QVD\VBAK.QVD(qvd)

;

[ZTRPSTTEMP]:

SQL Select *, Right([ZORDER],10) AS ZorderID

FROM ZTRPST;

[ZTRPST]: // Time Reports - Spread Time Posted

Load

ZorderID,

[ZTICKNO] as [Ticket],

Left([ZOBJECT],8) as EmpID,

Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],

Right([ZORDER],10) as [Contract],

[ZACTIVITY] as [Contrac Line],

[ZSTATUS] as [Status],

[ZWDATE] as [Date],

ZHOURS as [Z_Hours],

ceil(ZHOURS, .01) as [Hours],

round(ZHOURS, .01) as [Hours_Round],

floor(ZHOURS, .01) as [Hours_Floor]

Resident ZTRPSTTEMP

WHERE ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and

(ZSTATUS = 'C' or ZSTATUS = 'I')

AND Exists(ZorderID, VBELN)

// Or use below for Exists

// AND Exists(Right([ZORDER], 10), VBELN)

;

Drop Table [ZTRPSTTEMP];

STORE * FROM [ZTRPST] into QVD\ZTRPST.QVD

;

Exit Script;

Re: Script Logic

tmumaw — Fri, 26 Jan 2018 16:07:53 GMT

This part of the script runs fine however I get nothing out and I know there is data there.

[VBAKX]:
Load
KUNNR & VKORG & VTWEG AS %KEY_KNVV,
[VBELN],
[VBELN] as [%ContractDocument_Key],
Right([VBELN],8) as [Contract_ID],
[VKORG] as [%SalesOrganisation_Key],
[ERNAM] as [Created by],
[ERDAT] as [Contract Create Date],
[VTWEG] as [%DistributionChannel_Key],
[VBTYP] as [Doc Cat],
[AUART] as [Sales Document Type],
[KUNNR] as [Ship To],
[VKBUR] as [SalesOffice_Key],
[VKGRP] as [%SalesGroup_Key],
[AUDAT] as [Document Date],
[NETWR] as [Contract Net Value],
[WAERK] as [Doc Curr],
[GUEBG] as [Valid Frm Date],
year(GUEBG) as Valid_Year,
[GUEEN] as [Valid To Date],
[PS_PSP_PNR] as [WBS Element],
[ZZWBS],
If(Mid(ZZWBS,1,1) = 'W',1,0) as WBS_SW
;

SQL Select * from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC' or AUART = 'ZRPS' or AUART = 'ZBCS') //and (ZZWBS <> '00000000' or ZZWBS <> ' ')
and GUEEN >= '$(vYearEnd)'
;
//Store VBAK into QVD\VBAKX.QVD
;

NoConcatenate
VBAK:
Load *
Resident VBAKX
Where WBS_SW = '1'
;
Store VBAK into QVD\VBAK.QVD
;
Drop table VBAKX;
[ZTRPSTTemp]: // Time Reports - Spread Time Posted
Load
[ZTICKNO]   as [Ticket],
Right([ZORDER],10) & [ZACTIVITY] as [%ContractItemKey],
Right([ZORDER],10) as [%Contract],
[ZACTIVITY]   as [Contract Line],
[ZSTATUS]   as [Status],
[ZWDATE]   as [Date],
ZHOURS   as [Z_Hours],
ceil(ZHOURS, .01) as [Hours],
round(ZHOURS, .01) as [Hours_Round],
floor(ZHOURS, .01) as [Hours_Floor]
;
SQL Select ZTICKNO ZORDER ZWDATE ZHOURS ZACTIVITY ZSTATUS
from ZTRPST where ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;

[ZTRPST]:
Load *
RESIDENT ZTRPSTTemp
WHERE exists([VBELN], [%Contract])
;
STORE * FROM [ZTRPSTTemp] INTO QVD\ZTRPST.QVD;

Drop Table ZTRPSTTemp
;

exit SCRIPT;

Re: Script Logic

vishsaggi — Fri, 26 Jan 2018 17:27:45 GMT

Do not use exists and just load the data and see if you can see the same data values for VBELN and %Contract fields?

Your below SQL statement do not have commas for your fields. May be this is taken care in your actual script file? Just wanted to check.

SQL Select ZTICKNO ZORDER ZWDATE ZHOURS ZACTIVITY ZSTATUS
from ZTRPST where ZWDATE >= '$(vPostYear)' and ZEMPEQP = 'P' and
(ZSTATUS = 'C' or ZSTATUS = 'I')
;

Re: Script Logic

tmumaw — Fri, 26 Jan 2018 19:11:35 GMT

I commented out the exists statement and it runs fine with correct join.