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], ...
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?
"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
// end if
- Make a date field recognizable as a date:
date#([Field2_Doc. Date],'DD.MM.YYYY') as [Field2_Doc. Date],
Below solved the issue with moving tailing negative and convert from string to number.
(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).
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,
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,