Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
(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.
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] ;
Can you post some sample data, it's difficult to help without seeing what data you are trying to load!
Andy
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";
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] ;
Many routes to the city.. Please close this tread.
Hi Simon,
May I know which connector you using? OLAP connector?
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).