Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
Hi Sunil,
Below is a screenshot from the BW transaction RSA1. The fields I am having the issues with are the Min and Max Lot Sizes.
As you can see below, these fields are floating point numbers:
As you can see these are custom fields, however I cant see why that would be making the output into QV any different.
Regards,
Trevor
Hi Rakesh,
Thanks for the reply. I thought the format parameter (being '#,###') was for the output of the number and had no impact on the input? In any case I did give it a try but still no luck 😞
I also tried using the num# function without the format parameter and had the same result.
Im starting to get worried that my only option will be to do an inline table to manually calculate the numbers. This is an option I want to avoid as it will be a massive performance hit, given the volume of data I will be dealing with.
Could this problem be a result of the number of digits that are being loaded into the num function? Not that I know of any way to reduce the number of digits that are being pulled out of the BW field!
Regards,
Trevor
Hi All,
I had a bit of a play today with this problem by trying to replicate it in a non-BW loading environment. I created the following script:
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
FLOAT_TABLE:
LOAD * INLINE [
DATA
'3,00000000000000000000E+03'
];
TRANS_DATA:
LOAD num(DATA) as Float_Value
RESIDENT FLOAT_TABLE;
SET ThousandSep=',';
SET DecimalSep='.';
FLOAT_TABLE_EN:
LOAD * INLINE [
DATA_EN
'3.00000000000000000000E+03'
];
TRANS_DATA_EN:
LOAD num(DATA_EN) as Float_Value_En
RESIDENT FLOAT_TABLE_EN;
This works fine and can handle either loading with Euro number formats or non-Euro formats. I thought that this was the fix to the problem, as our BW system by default will display in the Euro format.
Unfortunately even when I applied the learnings from the above script (being to set the decimal separator correctly) it still had no effect. I even tried to apply the decimal separator logic when loading from the QVD the data is loaded into from BW. Nothing seems to work!
I even tried the following, probably more in desparation!
MATL_PLANT:
LOAD Material,
Plant,
[ABC Indicator],
[MRP Type],
[MRP Group],
[MRP Controller],
[Base UOM],
[Lot Size],
Text([Minimum Lot Size]) as [Minimum Lot Size],
Text([Maximum Lot Size]) as [Maximum Lot Size],
Text([Fixed Lot Size]) as [Fixed Lot Size]
FROM D:\Qlikview_QVD\BW\MD\BPP\QVD\0MATL_PLANT.qvd (qvd);
LEFT JOIN (MATL_PLANT)
LOAD Material,
Num([Minimum Lot Size],'#,##',',','.') as [Min Lot Size],
Num([Maximum Lot Size]) as [Max Lot Size],
Num([Fixed Lot Size]) as [Fix Lot Size]
RESIDENT MATL_PLANT;
I get the following result:
As you can see, the text versions show fine, but it cannot seem to handle the number conversion, even though when I manually set it using an inline statement it can convert it without any issues.
Any advice would be very much appreciated!
Regards,
Trevor
