Qlik Community

QlikView Connectors

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
samir_bihari
New Contributor

Fields not available after executing Script

Hi Experts ,

I am trying to connect to SAP BW query using Bex Connector and I am able to fetch the data after running the script as shown below :

Successful Script Execution.JPG

Now I close the pop up and Sheet Properties window appears where I should see the list of Available fields and then I can select the desired ones to get displayed in the sheet. But here I am getting the available fields empty and none of the fields are available  (Screenshot below ). Hence I am unable to proceed .

Capture.JPG

Any pointers on how to overcome this issue and get the field list displayed ?

Thanks in Advance

1 Solution

Accepted Solutions

Re: Fields not available after executing Script

Hi,

check your end line you are droping entire table and because of that you are not getting any field after successfull complitation of script.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
8 Replies

Re: Fields not available after executing Script

Can you post your complete script

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
samir_bihari
New Contributor

Re: Fields not available after executing Script

max dreamer wrote:

Can you post your complete script

Regards

Hi Max Dreamer ,

ZCOPA02_Q01_ST_AUTH : SAP BW Query Name

Connector Used : Qv Bex Connector

Below is the complete script :

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

CUSTOM CONNECT TO "Provider=QvBexConnector.exe;servertype=0;ASHOST=10.148.9.156;CLIENT=100;SYSNR=00;Timeout=3600;SNC_MODE=false;SNC_QOP=9;XUserId=bZMedJdIQBMKTaEW;XPassword=cDLJTZFMNLaEXYEV;";

[ZCOPA02_Q01_ST_AUTH]:

LOAD

[0CUSTOMER] as [Customer number],

[0SALES_OFF] as [Sales Office (Territory)],

[ZDIVISION] as [Division],

[0FISCYEAR] as [Year],

[00O2TEUVW6E5MFFKXRIU579Z3] as [Target],

[00O2TEUVW6E7D0RWWC3NTCGNQ] as [Net Sales Revenue],

[00O2TEUVW6E7G1N0V5IL04I4J] as [Pending Invoices],

[CURRENCY003] as [CURRENCY003 Target],

[UNIT003] as [UNIT003 Target],

[CURRENCY004] as [CURRENCY004 Net Sales Revenue],

[UNIT004] as [UNIT004 Net Sales Revenue],

[CURRENCY006] as [CURRENCY006 Pending Invoices],

[UNIT006] as [UNIT006 Pending Invoices];

// Uncomment appropriate lines and move the semicolon

// to load TEXT data for the dimensions that are known

// to include text: 

// [0CUSTOMER__TEXT] as [Customer number TEXT],

// [0SALES_OFF__TEXT] as [Sales Office (Territory) TEXT],

// [ZDIVISION__TEXT] as [Division TEXT],

// [0FISCYEAR__TEXT] as [Year TEXT];

SELECT [ZCOPA02_Q01_ST_AUTH]

DIMENSIONS (

  [0CUSTOMER],

  [0SALES_OFF],

  [ZDIVISION],

  [0FISCYEAR],

)

MEASURES (

  [00O2TEUVW6E5MFFKXRIU579Z3],

  [00O2TEUVW6E7D0RWWC3NTCGNQ],

  [00O2TEUVW6E7G1N0V5IL04I4J],

)

UNITS (

  [CURRENCY003.00O2TEUVW6E5MFFKXRIU579Z3],

  [UNIT003.00O2TEUVW6E5MFFKXRIU579Z3],

  [CURRENCY004.00O2TEUVW6E7D0RWWC3NTCGNQ],

  [UNIT004.00O2TEUVW6E7D0RWWC3NTCGNQ],

  [CURRENCY006.00O2TEUVW6E7G1N0V5IL04I4J],

  [UNIT006.00O2TEUVW6E7G1N0V5IL04I4J],

)

VARIABLES (

  [NAME=ZV_YEAR, SIGN=I, OPTION=EQ, LOW=K42016],

)

FROM [ZCOPA02];

STORE * FROM [ZCOPA02_Q01_ST_AUTH] INTO ZCOPA02_Q01_ST_AUTH.qvd;

DROP TABLE [ZCOPA02_Q01_ST_AUTH];

Thanks

Samir

Re: Fields not available after executing Script

Hi,

check your end line you are droping entire table and because of that you are not getting any field after successfull complitation of script.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
h_eskelinen
New Contributor III

Re: Fields not available after executing Script

Hey Samir,

the last line in your script drops the table you loaded (this line: DROP TABLE [ZCOPA02_Q01_ST_AUTH]Smiley Wink. Comment or remove it and the data will remain available.

samir_bihari
New Contributor

Re: Fields not available after executing Script

Thanks Max . It worked . I am just wondering that the script was automatically created by the wizard and I have not written it manually . Not sure why the DROP table command was there .

Re: Fields not available after executing Script

Hi,

In this code you are creating qvd which can be use in later application.

Here Purpose of drop table is reduce file size.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
h_eskelinen
New Contributor III

Re: Fields not available after executing Script

It would seem that the wizard is designed for an ETL process where one .qvw only extracts and stores the data in .qvd files and other .qvw files transform and load the data.

Re: Fields not available after executing Script

That's basic practice when using one of the SAP Connectors. SAP Connector is most often used in a first data stage where SAP data is read into QlikView and stored in QVDs. These QVDs will be used by the QlikView document in the next data stage to transform tables into QlikView tables or data marts.

There is no use for QlikView to keep those tables in the document as they do not form a data model. The end result is the set of QVDs that is created on disk.

Best,

Peter

Community Browser