5 Replies Latest reply: Jun 1, 2014 10:05 AM by whiteline _ RSS

    Issues in reloading csv file from stored QVD

      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 Mohammed

          Hi Madhu,

           

          Can you attached sample file with data.

           

          Regards

          ASHFAQ

            • Re: Issues in reloading csv file from stored QVD

              Hi Ashfaq,

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

               

              regards

              madhu

                • Re: Re: Issues in reloading csv file from stored QVD
                  Ashfaq Mohammed

                  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

                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: Issues in reloading csv file from stored QVD
                    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.