SAP Query Returns Negative Value

[JonSchwans]

In data load editor we iterate through a SAP BW query by create a variable for the yearperiod.  We then increment the period by 1.  Each yearperiod value writes a separate qvd, at the end of the load script we combine all the individual yearperiod qvds into a master qvd.  

The issue we have is, the data for the prior year, in this case 2022, comes back as a negative number that does not match any individual number.  For example prior year return -168,000.  If we run the query in BW Query designer for the same year and period the value returned is 106,970,042, which is the expected value.  Oddly when running the exact same query but for current year period data values return as expected.

Looking for any advice to help understand why prior year returns a "random" negative value, yet current year returns the correct value.

[cristianj23a]

Hello, your case happened to me several times and it always happens for these two reasons.

1.- When extracting the data from SAP and saving it in a QVD, the modifications that could have occurred after the first record in SAP were not considered and therefore they were not identified in qlik.

2.- In SAP there are two faces, what the end user sees and what it really is (The developer sees that). I mean that the transaction is responsible for transforming the data so that the user can see it in another way.
Example:

Internally, a numerical date is displayed that would be 45146 but at the time of viewing the transaction it is 08/08/2023; However when you extract qlik it comes with the internal data type which would be 45146.
In order for them to change it to the type of data you want, you must do it in the qlik script where you extract your SAP data.

If you see that this is not the case, could you give me more details to help you.

Regarts.

[JonSchwans]

thank you for the response and I sincerely appreciate the assistance.

I don't think either of these apply.  I'm not sure what additional information to provide.  We run query A for 2022 and we get negative numbers.  If we run query A for 2023 we get positive numbers.  The only difference in running query A is the Fiscal year.

[cristianj23a]

Hello Jon, when they saved the first QVD in 2022, did it already have negative numbers or did it change recently?

[JonSchwans]

This was not in use last year, just started this year.  Here is the code we are using to iterate through the SAP BW Query:   Works perfectly for 2023, but 2022 gives a "random" negative number

For y = 2022 to 2023 // Yearly variable

For p = 1 to 12 //Period Variable

Let vYearMonth = 'K1'& Num($(y)) & Num($(p),'000'); // Variable

LIB CONNECT TO 'SapBEx_mrs1sappbw02';

[FACT]:

LOAD 

[0PROD_HIER__ZMETHNIC] as [Promoted Group],

[0PROD_HIER__ZMPRDKA] as [Brand],

[0PROD_HIER__ZMSEGMENT] as [Marketing Team],

[0PROD_HIER__ZPRDVP] as [Product VP],

[0PROFIT_CTR] as [Sales Structure - Consolidated],

[ZHKUNNR] as [Customer Planning Hierarchy Key],

[0FISCPER3] as [Period],

[0FISCYEAR] as [Year],

[006EI4JMF4MCRQS05LGHO1S62] as [CY, NY,  LYs, Plan, Locks, Fcsts],

[006EI49TUX0YBX9XT87OL6AR4] as [Trade NUC],

[006EI49TUX0YBX9Y7J72B05WW] as [Working Trade Rate],

[006EI49TUX0YBX9ZASSK73KDS] as [Total Trade NUC],

[006EI49TUX0YBX9ZASSK743CG] as [Total Trade Rate],

[006EI49TUX0YBX9ZVBL3DJ3E8] as [Total NUC],

[006EI49TUX0YBX9ZVBL3DJMCW] as [Net Sales Rate],

[006EI4JCAPVFHRPY0ZQ8ZU0HD] as [Gross Sales],

[006EI4JCAPVFHRPY0ZQ8ZVY0H] as [Units],

[006EI4JCAPVFHRPY0ZQ8ZY86P] as [Cases],

[006EI4JCAPVFHRPY0ZQ902FW1] as [Trade],

[006EI4JCAPVFHRPY0ZQ906AY9] as [Working Net Sales],

[006EI4JCAPVFHRPY0ZQ906TWX] as [Net Sales],

[006EI4JCAPVFHRPY0ZQ907CVL] as [Trade Rate],

[006EI4JCAPVFHRPY0ZQ907VU9] as [Working Trade NUC],

[006EI4JCAPVFHRPY0ZQ908ESX] as [Total Non-Working],

[006EI4JCAPVFHRPY0ZQ908L4H] as [Working Trade],

[006EI4JCAPVFHRPY0ZQ90ACC1] as [Non-Working Corporate],

[006EI4JCAPVFHRPY0ZQ90DI41] as [Non-Working Trade],

[006EI4JCAPVFHRPY0ZQ90NIEP] as [Non-Working Supply Chain],

[0PROD_HIER__ZMETHNIC__TEXT] as [Promoted Group TEXT],

[0PROD_HIER__ZMPRDKA__TEXT] as [Brand TEXT],

[0PROD_HIER__ZMSEGMENT__TEXT] as [Marketing Team TEXT],

[0PROD_HIER__ZPRDVP__TEXT] as [Product VP TEXT],

[0PROFIT_CTR__TEXT] as [Sales Structure - Consolidated TEXT],

[ZHKUNNR__TEXT] as [Customer Planning Hierarchy - Consolidated TEXT],

[0FISCPER3__TEXT] as [Period TEXT],

[0FISCYEAR__TEXT] as [Year TEXT];

SELECT [ZS_QLIK_0016_ZSD_CP08_Q039]

DIMENSIONS (

[0PROD_HIER__ZMETHNIC], //Promoted Group

[0PROD_HIER__ZMPRDKA], //Brand

[0PROD_HIER__ZMSEGMENT], //Marketing Team

[0PROD_HIER__ZPRDVP], //Product VP

[0PROFIT_CTR], //Sales Structure - Consolidated

[ZHKUNNR], //Customer Planning Hierarchy - Consolidated

[0FISCPER3], //Period

[0FISCYEAR], //Year

[006EI4JMF4MCRQS05LGHO1S62], //CY, NY,  LYs, Plan, Locks, Fcsts

)

MEASURES (

[006EI49TUX0YBX9XT87OL6AR4], //Trade NUC

[006EI49TUX0YBX9Y7J72B05WW], //Working Trade Rate

[006EI49TUX0YBX9ZASSK73KDS], //Total Trade NUC

[006EI49TUX0YBX9ZASSK743CG], //Total Trade Rate

[006EI49TUX0YBX9ZVBL3DJ3E8], //Total NUC

[006EI49TUX0YBX9ZVBL3DJMCW], //Net Sales Rate

[006EI4JCAPVFHRPY0ZQ8ZU0HD], //Gross Sales

[006EI4JCAPVFHRPY0ZQ8ZVY0H], //Units

[006EI4JCAPVFHRPY0ZQ8ZY86P], //Cases

[006EI4JCAPVFHRPY0ZQ902FW1], //Trade

[006EI4JCAPVFHRPY0ZQ906AY9], //Working Net Sales

[006EI4JCAPVFHRPY0ZQ906TWX], //Net Sales

[006EI4JCAPVFHRPY0ZQ907CVL], //Trade Rate

[006EI4JCAPVFHRPY0ZQ907VU9], //Working Trade NUC

[006EI4JCAPVFHRPY0ZQ908ESX], //Total Non-Working

[006EI4JCAPVFHRPY0ZQ908L4H], //Working Trade

[006EI4JCAPVFHRPY0ZQ90ACC1], //Non-Working Corporate

[006EI4JCAPVFHRPY0ZQ90DI41], //Non-Working Trade

[006EI4JCAPVFHRPY0ZQ90NIEP], //Non-Working Supply Chain

)

VARIABLES (

[NAME=OFISPE01, SIGN=I, OPTION=EQ, LOW=$(vYearMonth)], //Fiscal Year/Period (O,O)

)

FROM [ZSD_CP08];

Store [FACT] into [lib://MRS15/Test/HQ_Sales_Data_$(vYearMonth).qvd];

Drop Table FACT;

Next p;

Next y;

[cristianj23a]

If there are no errors, then I recommend that you review with less data and explore the 2 years with examples in SAP
I'm sure it's something from your SAP, because all qlik does is extract what's in SAP, unless you apply a data format change in the script.
What I always do is a breakpoint to SAP

[JonSchwans]

🙂  Great minds think alike.  I did run the query for each year with far less data and got the same results.

Can you provide a further explanation/instructions on what you mean by a breakpoint to SAP?

[cristianj23a]

Now gradually increase the data, somewhere for sure it turns negative.

When negative appears, filter that data specifically and do your tests extracting that data, if you still see positive in SAP and when you extract in Qlik it goes negative, it is something internal to SAP, I recommend that you get support from someone from SAP in developer mode.

These cases are solved by testing and testing until the origin is found.

A break point is a way of knowing what codes are executed when using a transaction in SAP, although if it is a standard SAP transaction and not a Z, it is more difficult.

[JonSchwans]

My apology, I didn't communicate very well.   When I run with the minimum data possible I get negative for prior year and positive for current year.... so it's the same issue.

[cristianj23a · Accepted solution]

Did you try extracting one by one, that is, individually in a separate qvd, doing a simple select?

If you managed to do that and the same result still comes out, then perhaps it has to do with the logic of SAP, probably in 2021 they used it in a different way.