topic Re: Issues in reloading csv file from stored QVD in QlikView

Issues in reloading csv file from stored QVD

Sun, 01 Jun 2014 12:03:43 GMT

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

Re: Issues in reloading csv file from stored QVD

ashfaq_haseeb — Sun, 01 Jun 2014 12:08:15 GMT

Hi Madhu,

Can you attached sample file with data.

Regards

ASHFAQ

Re: Issues in reloading csv file from stored QVD

Sun, 01 Jun 2014 12:49:08 GMT

Hi Ashfaq,

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

regards

madhu

Re: Issues in reloading csv file from stored QVD

Sun, 01 Jun 2014 13:03:02 GMT

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

Re: Re: Issues in reloading csv file from stored QVD

ashfaq_haseeb — Sun, 01 Jun 2014 13:32:50 GMT

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

Re: Issues in reloading csv file from stored QVD

whiteline — Sun, 01 Jun 2014 14:05:46 GMT

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.