Found one issue with script load when I am loading % values directly from DB. Whenever we have 100% as value in any row that was coming as 1 instead of 100%. After searching on forums found one link: % is coming as 1 instead of 100% but there also no solution was provided. After doing some trial and error I was able to load the data as required. Below is the script which I used:
LOAD MetricId, Dual(MetricName,MetricId) as MetricName, kpimonth, Date(Trim(floor(Date("kpimonth",'MM/DD/YYYY'))),'MM/DD/YYYY') as [Date], if(Not IsNull(FinalVal),FinalVal&chr(32),FinalVal) as FinalVal;
LOAD MetricId, MetricName, kpimonth, FinalVal; SQL SELECT MetricId, MetricName, kpimonth, FinalVal FROM dbo."table1" where datediff(month,"kpimonth",GetDate())<13;
In the bold text above you can see I have concatenated chr(32) with the value and it works for me. Hope this helps others.