Issues in reloading csv file from stored QVD

[#1]

Hi All,

we have generated QVD's in csv format. when we open the csv file in Excel or notepad the amount fields are showing with

rupee sign ₹ as prefix of number.

When we reload the QVD into QVW the same data is showing with $ sign as prefix.

Below is the code iam using for QVD and CSV generation

STORE [Crm_quotes] into $(vQvdFile) (qvd);

STORE [Crm_quotes] into $(vQvdFile).csv (UTF8, txt,  delimiter is ',');

Regards

madhu

[ashfaq_haseeb]

Hi Madhu,

Can you attached sample file with data.

Regards

ASHFAQ

[#3]

Hi Ashfaq,

I have uploaded the sample file with data, please check  and advise...

regards

madhu

[#4]

There is nothing wrong when the data is loaded from the database and then stored in the in QVD, howeve when stored as txt as csv file, all the fields which were of currency/money type  are appended with the currency symbol.

Currently it is showing indian rupee symbol, however even that default server currency is $, so not sure from where QV is picking up that symbol.

Here are some sample rows

PrimaryKey,CreatedTimeStamp,ModifiedTimeStamp,PriceList,Currency,CreatedBy,Opportunity,SalesTerritory,AccountOwner,Modifiedby,OpportunityType,Owner,Account,QuoteNumber,RevisionNumber,Quote,TotalAmount,TotalLineItemAmount,TotalLineItemDiscountAmount,TotalAmountLessFreight,EffectiveFrom,TotalTax,TotalDiscountAmount,State,Status,Customer,ActualQuoteMargin,ApprovalStatus,DocumentStage,MultiYearContract,NetMargin,QuoteID,QuoteTotalCost,ContractName,PaymentTerms,TotalNetProfit,Delivery,Validity

{BFAB8018-5998-E311-8FDE-AC162D7273F2},18-02-2014 04:56:59,01-06-2014 11:31:05,2014 PriceList,Emirati Dirham (د.إ.‏),Shenouda Wadie,Computer Care - TA 3051ci,Abu Dhabi,,CRM .,Transactional,Shenouda Wadie,COMPUTER CARE LLC,QUO-02347-W9D6R2,0,TEST,"₹ 333,229.60","₹ 333,229.60",₹ 0.00,"₹ 333,229.60",,₹ 0.00,₹ 0.00,Draft,In Progress,COMPUTER CARE LLC,₹ 0.71,Pending Approval,Draft,No,70.6671376132,20140001326,"₹ 90,782.97",No,No,"₹ 235,483.82",214680000,214680014

{5285C982-72E5-E311-8FDE-AC162D7273F2},27-05-2014 07:42:54,01-06-2014 04:25:58,2014 PriceList,Emirati Dirham (د.إ.‏),Mahantayya Shankarayya,Task-1800,Dubai,CRM .,Mahantayya Shankarayya,Transactional,Mahantayya Shankarayya,EBRAHIM OFFICE SOLUTIONS FZE,QUO-02958-M6R8K4,0,Task-1800,"₹ 82,800.00","₹ 82,800.00","₹ 83,450.00","₹ 82,800.00",,₹ 0.00,"₹ 83,450.00",Closed,Revised,EBRAHIM OFFICE SOLUTIONS FZE,₹ 0.03,Pending Approval,Draft,No,2.5827294686,20140001960,"₹ 80,661.50",No,No,"₹ 2,138.50",214680000,214680014

{35CD80DA-39E7-E311-8FDE-AC162D7273F2},29-05-2014 14:02:25,01-06-2014 04:26:44,2014 PriceList,Emirati Dirham (د.إ.‏),Barun Mishra,F.G.P Requirement for Scanner,Abu Dhabi,Sabique Ponnambath,Barun Mishra,Transactional,Barun Mishra,FIRST GULF BANK,QUO-02971-R3P1J1,0,F.G.P Requirement for Scanner,"₹ 3,790.00","₹ 3,790.00","₹ 4,050.00","₹ 3,790.00",,₹ 0.00,"₹ 4,050.00",Won,Won,FIRST GULF BANK,₹ 1.00,Approved,Draft,No,100.0000000000,20140001974,₹ 0.00,No,No,"₹ 3,790.00",214680000,214680014

{39F0599B-40E7-E311-8FDE-AC162D7273F2},29-05-2014 14:50:43,01-06-2014 06:05:53,2014 PriceList,Emirati Dirham (د.إ.‏),Saiju Kuriakose,Offer  for  FS 9130 DN  with  aditional  4 toners,Dubai,Saiju Kuriakose,Saiju Kuriakose,Transactional,Saiju Kuriakose,EMIRATES BUILDING SYSTEMS,QUO-02972-B0M3K4,0,Offer  for  FS 9130 DN  with  aditional  4 toners,"₹ 12,620.00","₹ 12,620.00",₹ 435.00,"₹ 12,620.00",,₹ 0.00,₹ 435.00,Active,In Progress,EMIRATES BUILDING SYSTEMS,₹ 0.80,Pending Approval,Draft,No,80.0867670365,20140001975,"₹ 2,513.05",No,No,"₹ 10,106.95",214680000,214680014

[ashfaq_haseeb]

Hi,

may be like this, you have your amount in text format, which you need to convert to money.

Please find the attached application.

you can use this code in your front end.

=money(sum(num#(QuoteTotalCost,'₹#,##0.00')),'₹#,##0.00')

If you want to do the same in backend then use the below code.

LOAD PrimaryKey,

     CreatedTimeStamp,

     ModifiedTimeStamp,

     PriceList,

     Currency,

     CreatedBy,

     Opportunity,

     SalesTerritory,

     AccountOwner,

     Modifiedby,

     OpportunityType,

     Owner,

     Account,

     QuoteNumber,

     RevisionNumber,

     Quote,

     TotalAmount,

     TotalLineItemAmount,

     TotalLineItemDiscountAmount,

     TotalAmountLessFreight,

     EffectiveFrom,

     TotalTax,

     TotalDiscountAmount,

     State,

     Status,

     Customer,

     ActualQuoteMargin,

     ApprovalStatus,

     DocumentStage,

     MultiYearContract,

     NetMargin,

     QuoteID,

     QuoteTotalCost,

     money((num#(QuoteTotalCost,'₹#,##0.00')),'₹#,##0.00') as QuoteTotalCost1, 

     ContractName,

     PaymentTerms,

     TotalNetProfit,

     Delivery,

     Validity

FROM

[..\..\Downloads\qlikviewcsv.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards

ASHFAQ

[whiteline]

Hi.

When you load the data from the database the currency amount is stored as simple text. The reason is that QV can't recognize the number format.

You can set the appropriate default numeric format with variables (at the script start) or you can use conversion functions like num#(). Then you can store and load the numbers properly.