SAP Connector - negative numbers / number conversion

[#1]

Dear all,

I have before had good luck with getting help here - so I try again.

I have been building report via export from SAP to Excel and load to QlikView.

Now I want to use the SAP Connector to access my predefined SAP-report but it gives me quite some challenges.

With Excel load I have:

With SAP Connector I get:

The date I have managed to convert to something useful. However the numbers trick me quite a bit.

I have tried to use the IF-statements found in a manual somewhere but this is what I get (different data but as you can see Sales and Qty is converted wrongly 20 becomes 20000 etc.):

Please tell me there is something very smart in QlikView to convert SAP (spool data?) into something useful. How to I trim numbers correct and get rid of trailing negative - so it is similar to what I have with Excel.

Thanks in advance.

[Peter_Cammaert]

Left-aligned values (second screenshot) mean that this icolumn contains text, not numbers. QlikView has to convert these strings into numerical values, and has problems with thousand and decimal separators.

Use the num#() function with appropriate format strings to properly convert strings into numbers. The following function can be used in a preceding Load for every "amount" column:

   ... num#([Sales Quantity], '#.###,00', ',', '.') AS [Sales Quantity], ...

Best,

Peter

[#3]

Hi,

Thanks for helping me here. I think it was a step in the right direction. I build the QVD by reading all numbers as you have described above. It now looks aligned to the right.

However the numbers are still interpreted wrongly

When building the QlikMart I use below script to read from the QVD and move the trailing minus/negative to the front. I have tried to do both operations when read/build the QVD but it don't work and too complex for my skill level to troubleshoot.

I have tried several combinations of Num# when read/build the QVD but without success.

SAP returns Sales Qty. as 20,000 which Excel correctly reads as 20 (I think I constantly gets 20000).

SAP returns Net Sales as 1.310,52 which Excel correctly reads as 1310.52

And then the 87,66- is -87.66 in Excel (using the script the minus is moved but the numbers are messed up).

(The date I have managed to get converted correctly using a combination of Num and Date)

Any bright ideas?

Thanks

/KG

"Since all data comes from the spool file, the data types are not known to the connector, which means it cannot modify fields according to data type as the other connectors can. This means that negative field values are shown as in SAP with the minus sign at the end of the field (for example, 12256-) and that date fields are not recognized by QlikView as dates. This can be handled using scripting in the load statement.

• Move the minus sign to the front of the field:

  if (right([Field1_Amount],1)='-', (left([Field1_Amount],(len([Field1_Amount])-1)))*-1  

// else

  , replace([Field1_Amount],',',''))

// end if

    as Local_Curr,

• Make a date field recognizable as a date:

date#([Field2_Doc. Date],'DD.MM.YYYY') as [Field2_Doc. Date],

"

[#4 · Accepted solution]

Below solved the issue with moving tailing negative and convert from string to number.

Case closed    

(I think in the QVD negative numbers get , as decimal separator and positive . but when I load into QlikMart everything looks good. So I just leave it for now).

   [Sales Quantity],
        if(Right([Sales Quantity],1) = '-',
              Num(Num#(Mid([Sales Quantity], 1, Len([Sales Quantity])-1),'#.###,000',',','.')) * -1,  // Negativ values
             Num(Num#([Sales Quantity],'#.###,000',',','.'))) as NewQty,
       
        [Gross Sales],
        if(Right([Gross Sales],1) = '-',
              Num(Num#(Mid([Gross Sales], 1, Len([Gross Sales])-1),'#.###,00',',','.')) * -1,  // Negativ values
             Num(Num#([Gross Sales],'#.###,00',',','.'))) as NewGS,