Different values in an original excel file and in qvd - why?

[#1]

Hello,

I have the following problem:

in excel file i have a column named gpn.

GPN is a calculated column, but in excel it appears as a result of calculation.

So in this column i have value 5.6 for some row. Why in qvd this value turns to be different?

In qvd this value turns to be 4.75....

Thanks a lot!

[whiteline]

Hi.

As you don't provide any info about what you've done with that excel to store it in qvd, the question becomes rhetorical...

[its_anandrjs]

Hi,

I suggest you have to use Num( ) function on the field that carries that desimal values.

HTH

Regards,

Anand

[#4]

Hi,

where should I add it?

While loading excel to qvd or in qliview UI?

here is my loading of excel to qvd (this is a loop of number of files. GPN is field 58):

SET

FilePath='*.csv';





for each File in filelist ('C:\Users\innas\Desktop\Qvd\QV_new\daily\' & FilePath)



let



vFileBaseName = SubField( File,'\',SubStringCount(File,'\')+1);



let



vFileName = left ( vFileBaseName,FindOneOf(vFileBaseName, '.', SubStringCount(vFileBaseName, '.'))-1);



    SET sFile = '$(File)';

    Directory;

   NetworkDomainDailyData:

    LOAD

   

@1 as AdvertiserLineId,

@2 as BuyerImpressions,

@3 as BuyerClicks,

@4 as BuyerConversions,

@5 as GrossRevenue,

@6 as GrossCost,

@7 as GrossProfit,

@8 as Revenue,

@9 as Country,

@10 as Date,

@11 as ServingFee,

@12 as CampaignManager,

@13 as Seat,

@14 as ALName,

@15 as AIOName,

@16 as Price,

@17 as LocalPrice,

@18 as ETName,

@19 as AName,

@20 as PTName,

@21 as IOId,

@22 as campaign_id_in_media,

@23 as LI_id,

@24 as AdvertiserId,

@25 as BP_type_name,

@26 as Integration_code,

@27 as Internal_type_name,

@28 as crm_id,

@29 as Parent_name,

@30 as SP_name,

@31 as BP_kickback,

@32 as Currency_id,

@33 as IsNew,

@34 as company,

@35 as operational_team,

@36 as Sub_Type,

@37 as OfferType,

@38 as Bill_on_id,

@39 as BillOn,

@40 as AgencyCommission,

@41 as AgencyDiscount1,

@42 as AgencyDiscount2,

@43 as BrokerFee,

@44 as IO_KickBack,

@45 as BrandSafe,

@46 as Profit_share,

@47 as brokerprofitshare,

@48 as PPublisherName,

@49 as AMName,

@50 as Publisher_id,

@51 as ETName2,

@52 as Name,

@53 as PublisherLineId,

@54 as PTName2,

@55 as AMName2,

@56 as PSub_Type,

@57 as RevenueNet,

@58 as GPN,

@59 as Size,

@60 as PLI_id











    FROM

    [$(sFile)]

    (txt, codepage is 1252, no labels, delimiter is ',');



store * from NetworkDomainDailyData into C:\Users\innas\Desktop\Qvd\Sql_Data\$(vFileName).qvd(qvd);

drop table NetworkDomainDailyData;



    next

   
Thanks !

[#5]

Hi,

here is my loading of excel file to qvd (this is a loop of number of files. GPN is field 58): :

SET

FilePath='*.csv';





for each File in filelist ('C:\Users\innas\Desktop\Qvd\QV_new\daily\' & FilePath)



let



vFileBaseName = SubField( File,'\',SubStringCount(File,'\')+1);



let



vFileName = left ( vFileBaseName,FindOneOf(vFileBaseName, '.', SubStringCount(vFileBaseName, '.'))-1);



    SET sFile = '$(File)';

    Directory;

   NetworkDomainDailyData:

    LOAD

   

@1 as AdvertiserLineId,

@2 as BuyerImpressions,

@3 as BuyerClicks,

@4 as BuyerConversions,

@5 as GrossRevenue,

@6 as GrossCost,

@7 as GrossProfit,

@8 as Revenue,

@9 as Country,

@10 as Date,

@11 as ServingFee,

@12 as CampaignManager,

@13 as Seat,

@14 as ALName,

@15 as AIOName,

@16 as Price,

@17 as LocalPrice,

@18 as ETName,

@19 as AName,

@20 as PTName,

@21 as IOId,

@22 as campaign_id_in_media,

@23 as LI_id,

@24 as AdvertiserId,

@25 as BP_type_name,

@26 as Integration_code,

@27 as Internal_type_name,

@28 as crm_id,

@29 as Parent_name,

@30 as SP_name,

@31 as BP_kickback,

@32 as Currency_id,

@33 as IsNew,

@34 as company,

@35 as operational_team,

@36 as Sub_Type,

@37 as OfferType,

@38 as Bill_on_id,

@39 as BillOn,

@40 as AgencyCommission,

@41 as AgencyDiscount1,

@42 as AgencyDiscount2,

@43 as BrokerFee,

@44 as IO_KickBack,

@45 as BrandSafe,

@46 as Profit_share,

@47 as brokerprofitshare,

@48 as PPublisherName,

@49 as AMName,

@50 as Publisher_id,

@51 as ETName2,

@52 as Name,

@53 as PublisherLineId,

@54 as PTName2,

@55 as AMName2,

@56 as PSub_Type,

@57 as RevenueNet,

@58 as GPN,

@59 as Size,

@60 as PLI_id











    FROM

    [$(sFile)]

    (txt, codepage is 1252, no labels, delimiter is ',');



store * from NetworkDomainDailyData into C:\Users\innas\Desktop\Qvd\Sql_Data\$(vFileName).qvd(qvd);

drop table NetworkDomainDailyData;



    next

   

[#6]

Hi,

I think its.anandrjs is right about using num(). It should most likely solve the issue.

You could use it in the script while loading the excel file. Just prefix num to @58 as shown below.

num(@58) as GPN

You can also try verifying the number format in excel file if that does not work.

Regards,

-Khaled

[#7]

Hi,

I tried it now:

num (@58) as GPN,

It did not help, but what helped is - turning this column to number format!

Thanks!

[#8]

Hi,

Not sure if this will help. But, if you mean that the decimal values are truncated, you might try this

num(@58,'#,##0.##')

Regards,

-Khaled

[#9]

Hi,

it did not help..

[#10]

Hi,

I would suggest you to upload the QlikView document with the excel file (or a sample of it) so that the community experts can get a better insight into the root of the problem.

Thanks,

-Khaled.