topic Re: How to get Key & Short/Medium/Long Text forms of characteristics from BW into Qlik? in Connectivity & Data Prep

How to get Key & Short/Medium/Long Text forms of characteristics from BW into Qlik?

quriouss — Fri, 27 Mar 2015 10:31:53 GMT

(I posted this in the "SAP" forum, but there's not much happening over there).

I am still new to Qlik (in this case, QlikSense) and am working out the nuances of connection to SAP BW.

I'm now stuck on getting the correct form of the SAP Characteristics. Here is my very simple extract script (using the built-in SAP BW connector).

Load

"[Measures].[DJP2RBDKTLYQ]" as "Invoice Value",

"[ZBRANCH].[LEVEL01].[MEMBER_CAPTION]" as "Branch Name";

SELECT NON EMPTY { [Measures].[DJP2RBDKTLYQ]} ON COLUMNS,

NON EMPTY {[ZBRANCH].[LEVEL01].MEMBERS} ON ROWS

FROM [M_INVOICES/Y_INVOICES_BY_BRANCH] ;

This works fine, but my "Branch Name" is appearing as text (London, Paris, Munich, etc). I want to display them as Branch Numbers (100, 200, 300, etc).

I can change [MEMBER_CAPTION] to [UNIQUE_MEMBER] but then I get [zbranch].100 [zbranch].200 (etc.) which looks ugly.

I've also tried adding the line;

DIMENSION PROPERTIES [0ZBRANCH].[20ZBRANCH]

but the extract fails with a syntax error.

Any suggestions I can ponder on over the weekend?

Many thanks.

Re: How to get Key & Short/Medium/Long Text forms of characteristics from BW into Qlik?

awhitfield — Fri, 27 Mar 2015 10:53:26 GMT

Can you post some sample data, it's difficult to help without seeing what data you are trying to load!

Andy

Re: How to get Key & Short/Medium/Long Text forms of characteristics from BW into Qlik?

Fri, 27 Mar 2015 11:01:24 GMT

As you said that you can can change [MEMBER_CAPTION] to [UNIQUE_MEMBER] but then I get [zbranch].100 [zbranch].200 (etc.)

use KeepChar() function as shown below.

KeepChar("[ZBRANCH].[LEVEL01].[UNIQUE_MEMBER] " , 1234567890) as "Branch Name";

Re: How to get Key & Short/Medium/Long Text forms of characteristics from BW into Qlik?

quriouss — Fri, 27 Mar 2015 12:57:36 GMT

I think I've got what I want. I needed to add a DIMENSION statement to make sure I was getting both forms of the output from BW (and then do a string manipulation to get the final correct string).

Next steps are to (a) work out how to differentiate between Short/Medium/Long Texts, and also to read up on the syntax of DIMENSION statements in MDX

Load

"[Measures].[DJP2RBDKTLYQ]" as "Invoice Value",

MID ("[ZBRANCH].[LEVEL01].[MEMBER_UNIQUE_NAME]" ,14, 6) as "Branch Name";

SELECT NON EMPTY { [Measures].[DJP2RBDKTLYQ]} ON COLUMNS,

NON EMPTY {[ZBRANCH].[LEVEL01].MEMBERS}

DIMENSION PROPERTIES MEMBER_UNIQUE_NAME,MEMBER_CAPTION

ON ROWS

FROM [M_INVOICES/Y_INVOICES_BY_BRANCH] ;

Re: How to get Key & Short/Medium/Long Text forms of characteristics from BW into Qlik?

Fri, 27 Mar 2015 13:02:02 GMT

Many routes to the city.. Please close this tread.

Re: How to get Key & Short/Medium/Long Text forms of characteristics from BW into Qlik?

nicholas5141 — Sat, 22 Apr 2017 08:37:21 GMT

Hi Simon,

May I know which connector you using? OLAP connector?

Re: How to get Key & Short/Medium/Long Text forms of characteristics from BW into Qlik?

quriouss — Tue, 30 May 2017 13:55:15 GMT

Apologies for the very late reply - I haven't logged in for some time.

The connector in use for the statement is actually the BEx connector that comes with the SAP Business Explorer client.

We also have the Qlik SAP connectors, but we started with the BEx connector (since we already had it so it didn't cost anything).