Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
quriouss
Creator III
Creator III

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

(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.

1 Solution

Accepted Solutions
quriouss
Creator III
Creator III
Author

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] ;

View solution in original post

6 Replies
awhitfield
Partner - Champion
Partner - Champion

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

Andy

Not applicable

Hi

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";

quriouss
Creator III
Creator III
Author

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] ;

Not applicable

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

nicholas5141
Partner - Creator
Partner - Creator

Hi Simon,

May I know which connector you using? OLAP connector?

quriouss
Creator III
Creator III
Author

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).