9 Replies Latest reply: Jan 21, 2013 3:51 PM by ALEXANDRE PERROT RSS

    Concatenate Load from QVD file using Where Clause

      Ok. I have spent a lot of time trying to see what is going on here. I am desperate. I need somebody to help me.

       

      I want to fetch data from a remote/ODBC database and make sure that I use incremental fetches. Only new or modified records to be retrieved.

       

      Here is my script:

       

       

       

      SET ThousandSep='.';
      SET DecimalSep=',';
      SET MoneyThousandSep='.';
      SET MoneyDecimalSep=',';
      SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
      SET TimeFormat='h:mm:ss TT';
      SET DateFormat='D/MM/YYYY';
      SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
      SET MonthNames='Ιαν;Φεβ;Μαρ;Απρ;Μαϊ;Ιουν;Ιουλ;Αυγ;Σεπ;Οκτ;Νοε;Δεκ';
      SET DayNames='Δευ;Τρι;Τετ;Πεμ;Παρ;Σαβ;Κυρ';
      
      
      LET LastExecTime = ReloadTime();
      
      
      ODBC CONNECT TO CrmReplication (XUserId is exxxxxxxxxxxxxx, XPassword is XcDJZxxxxxxxxxxxxxxxxxx);
      FlightReservations:
      SQL SELECT 
          crm_replication.dbo.New_FlightReservationBase.New_FlightReservationId,
          CreatedBy,
          CreatedOn,
          DeletionStateCode,
          ExchangeRate,
          ImportSequenceNumber,
          ModifiedBy,
          ModifiedOn,
          OverriddenCreatedOn,
          OwningBusinessUnit,
          OwningUser,
          StateCode,
          StatusCode,
          TransactionCurrencyId,
          VersionNumber,
          New_AffiliateId,
          New_Airline,
          New_AirlineFee,
          new_airlinefee_Base,
          New_airlinetxt,
          New_Bank,
          New_BankAccount,
          New_BankBeneficiary,
          New_BankCountry,
          New_bankswiftbiccode,
          New_BinName,
          New_binnumber,
          New_BinPhone,
          New_BrowserAcceptedLanguages,
          New_BSPRefundApplicationNumber,
          New_callcenterfee,
          new_callcenterfee_Base,
          New_CancellationDate,
          New_cancellationfeeappliesonrefund,
          New_CarrierContact,
          New_ccholder,
          New_CCPayAttempts,
          New_City,
          New_commissionper,
          New_CorporateCustomerId,
          New_Country,
          New_DateTimeissued,
          New_DeliveryCost,
          New_DeliveryType,
          New_DepartureDate,
          New_DiscountError,
          new_discounterror_Base,
          New_entryurl,
          New_entryurlstring,
          New_eticketsent,
          New_ExtraCharge,
          new_extracharge_Base,
          New_ExtraChargeDate,
          New_extrarefundstatus,
          New_extrarefundtocustomer,
          new_extrarefundtocustomer_Base,
          New_extrarefundtocustomerdate,
          New_FareBase,
          New_FareRules,
          New_FareValue,
          new_farevalue_Base,
          New_ForwardedAddress,
          New_fraudpoints,
          New_FraudSummary,
          New_FResContactId,
          New_From,
          New_GDS,
          New_IPAddress,
          New_IPCountry,
          New_IssuerBank,
          New_Keyword,
          New_LeadId,
          New_Leadtype,
          New_MCOVMPDNumber,
          New_MCOVMPDValue,
          new_mcovmpdvalue_Base,
          New_name,
          New_Organic_CPC,
          New_PaidPrice,
          New_PaidPriceCurrency,
          New_PassangerName,
          New_PassangersNo,
          New_PaymentDate,
          New_PaymentDue,
          New_PaymentMethodFee,
          new_paymentmethodfee_Base,
          New_PaymentType,
          New_PlatiingCarrier,
          New_PostalCode,
          New_refferingurl,
          New_Refund,
          new_refund_Base,
          New_RefundAgent,
          new_refundagent_Base,
          New_RefundAgentId,
          New_RefundDate,
          New_RefundStatus,
          New_ReissueNewTicketNumber,
          New_ReservationReference,
          New_ReservationStatus,
          New_ReturnDate,
          New_Segments,
          New_Sentdate,
          New_SentforInvoice,
          New_ServiceFee,
          new_servicefee_Base,
          New_servicefeeisrefundable,
          New_ShouldBlock,
          New_SkipFraud,
          New_SMS,
          New_Steet2,
          New_Street,
          New_SupplierAuthorization,
          New_SupplierAuthorizationDate,
          New_Taxes,
          new_taxes_Base,
          New_Telephone,
          New_TicketNumbers,
          New_TicketPrice,
          new_ticketprice_Base,
          New_TicketType,
          New_To,
          New_TravelInsuranceId,
          New_UpdatedFromGDS,
          New_UserAgent,
          New_VendorLocator,
          New_VoucherNo,
          New_website
      FROM crm_replication.dbo.New_FlightReservationBase
      JOIN crm_replication.dbo.New_FlightReservationExtensionBase
      ON crm_replication.dbo.New_FlightReservationBase.New_FlightReservationId = 
         crm_replication.dbo.New_FlightReservationExtensionBase.New_FlightReservationId
      ;
      
       CONCATENATE LOAD New_FlightReservationId,
          CreatedBy,
          CreatedOn,
          DeletionStateCode,
          ExchangeRate,
          ImportSequenceNumber,
          ModifiedBy,
          ModifiedOn,
          OverriddenCreatedOn,
          OwningBusinessUnit,
          OwningUser,
          StateCode,
          StatusCode,
          TransactionCurrencyId,
          VersionNumber,
          New_AffiliateId,
          New_Airline,
          New_AirlineFee,
          new_airlinefee_Base,
          New_airlinetxt,
          New_Bank,
          New_BankAccount,
          New_BankBeneficiary,
          New_BankCountry,
          New_bankswiftbiccode,
          New_BinName,
          New_binnumber,
          New_BinPhone,
          New_BrowserAcceptedLanguages,
          New_BSPRefundApplicationNumber,
          New_callcenterfee,
          new_callcenterfee_Base,
          New_CancellationDate,
          New_cancellationfeeappliesonrefund,
          New_CarrierContact,
          New_ccholder,
          New_CCPayAttempts,
          New_City,
          New_commissionper,
          New_CorporateCustomerId,
          New_Country,
          New_DateTimeissued,
          New_DeliveryCost,
          New_DeliveryType,
          New_DepartureDate,
          New_DiscountError,
          new_discounterror_Base,
          New_entryurl,
          New_entryurlstring,
          New_eticketsent,
          New_ExtraCharge,
          new_extracharge_Base,
          New_ExtraChargeDate,
          New_extrarefundstatus,
          New_extrarefundtocustomer,
          new_extrarefundtocustomer_Base,
          New_extrarefundtocustomerdate,
          New_FareBase,
          New_FareRules,
          New_FareValue,
          new_farevalue_Base,
          New_ForwardedAddress,
          New_fraudpoints,
          New_FraudSummary,
          New_FResContactId,
          New_From,
          New_GDS,
          New_IPAddress,
          New_IPCountry,
          New_IssuerBank,
          New_Keyword,
          New_LeadId,
          New_Leadtype,
          New_MCOVMPDNumber,
          New_MCOVMPDValue,
          new_mcovmpdvalue_Base,
          New_name,
          New_Organic_CPC,
          New_PaidPrice,
          New_PaidPriceCurrency,
          New_PassangerName,
          New_PassangersNo,
          New_PaymentDate,
          New_PaymentDue,
          New_PaymentMethodFee,
          new_paymentmethodfee_Base,
          New_PaymentType,
          New_PlatiingCarrier,
          New_PostalCode,
          New_refferingurl,
          New_Refund,
          new_refund_Base,
          New_RefundAgent,
          new_refundagent_Base,
          New_RefundAgentId,
          New_RefundDate,
          New_RefundStatus,
          New_ReissueNewTicketNumber,
          New_ReservationReference,
          New_ReservationStatus,
          New_ReturnDate,
          New_Segments,
          New_Sentdate,
          New_SentforInvoice,
          New_ServiceFee,
          new_servicefee_Base,
          New_servicefeeisrefundable,
          New_ShouldBlock,
          New_SkipFraud,
          New_SMS,
          New_Steet2,
          New_Street,
          New_SupplierAuthorization,
          New_SupplierAuthorizationDate,
          New_Taxes,
          new_taxes_Base,
          New_Telephone,
          New_TicketNumbers,
          New_TicketPrice,
          new_ticketprice_Base,
          New_TicketType,
          New_To,
          New_TravelInsuranceId,
          New_UpdatedFromGDS,
          New_UserAgent,
          New_VendorLocator,
          New_VoucherNo,
          New_website
      FROM [flight_reservations.qvd]
      WHERE Not Exists(New_FlightReservationId);
      
      STORE FlightReservations INTO flight_reservations.qvd;
      

       

      The problem is that the CONCATENATE LOAD command fails with the following error:

       

       

      Cannot open file 'C:\ProgramData\QlikTech\Documents\flight_reservations.qvd
      WHERE NOT Exists' The filename, directory name, or volume label syntax is incorrect.
      

       

      as if the WHERE NOT Exists is part of the filename that it is trying to open

       

      Any help would be greatly appreciated.

       

      Panayotis Matsinopoulos

        • Re: Concatenate Load from QVD file using Where Clause
          Diamantis Archontoglou

          Παναγιώτη

           

          The primary key on your Table does not seem to match. On the select command the primary key as a field

          is different. Notmally you should have something like this:

           

           

          QV_Table:
          SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

           

          WHERE ModificationTime >= #$(LastExecTime)#; 

           

          Concatenate 

          LOAD PrimaryKey, X, Y FROM File.QVD 

          WHERE NOT Exists(PrimaryKey); 

           

          STORE QV_Table INTO File.QVD; 

           

          Additionally you should also have some logic regarding the existance of the QVD File. something like this:

           

          LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

          IF $(vQvdExists) THEN

          CONCATENATE ($(vTableName)) LOAD * FROM $(vQvdFile) (qvd)

          WHERE NOT exists($(vPK)) // Load only QVD rows that were not already loaded in the data load.

          ;

          END IF

           

          Διαμαντής

            • Re: Concatenate Load from QVD file using Where Clause

              Hi Diamanti,

               

              Thanks for answer, but still I see a lot of problems:

               

              1) Your 1st CONCATENATE does not have a table argument. Your 2nd does have one.

              2) Your 1st LOAD ... FROM does not have the (qvd) specification. Your 2nd does.

              3) You script on the date on the where clause has problem with SQL Server but I think that I can handle this (not sure...but minor).

               

              To make the long story short, I am now trying the following script:

               

               

              SET ThousandSep='.';
              SET DecimalSep=',';
              SET MoneyThousandSep='.';
              SET MoneyDecimalSep=',';
              SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
              SET TimeFormat='h:mm:ss TT';
              SET DateFormat='D/M/YYYY';
              SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
              SET MonthNames='Ιαν;Φεβ;Μαρ;Απρ;Μαϊ;Ιουν;Ιουλ;Αυγ;Σεπ;Οκτ;Νοε;Δεκ';
              SET DayNames='Δευ;Τρι;Τετ;Πεμ;Παρ;Σαβ;Κυρ';
              
              
              ODBC CONNECT TO [ITDEVELOP06-PC-Test] (XUserId is QKGxxxxxxx);
              Products:
              SQL SELECT id,  
                  name,
                  dt_create,
                  dt_update    
              FROM Test.dbo.Products
              Where dt_update >= convert(datetime, '$(LastExecTime)')
              ;
              
              
              let qvd_file = 'test_qvd.qvd';
              let qvd_exists = if(FileSize($(qvd_file))>0, -1, 0);
              if $(qvd_exists) then
               Concatenate
               Load Id, name, dt_create, dt_update from test_qvd.qvd(qvd)
               where not exists(id);
              end if
              store Products into $(qvd_file) (qvd);
              

               

              This ALMOST works. The problem is that it never gets into the if block statements. The $(qvd_exists) is always 0. I do not know why. The store command that I have, which takes the same argument ( $(qvd_file) ) works perfect. It creates the file.

                • Re: Concatenate Load from QVD file using Where Clause
                  Rob Wunderlich

                  You need single quotes around the qvd_file variable.

                   

                  if(FileSize('$(qvd_file)')...

                   

                  -Rob

                  • Re: Concatenate Load from QVD file using Where Clause
                    Diamantis Archontoglou

                    Well it does work like this:

                    Try it once with this code and then add one record to inline statement and run it again. The first time the concatenate will not run.

                    The second time it will sep into the block.

                     

                    Products:
                    LOAD * INLINE [
                        id, desc
                        10, des1
                        20, des2
                        30, des3
                        40, des4
                        50, des5
                    ];

                    set qvd_file = test_qvd.qvd;

                    IF FileSize('$(qvd_file)') > 0 THEN
                    SET QVD_EXISTS=1;  // true
                    ELSE
                    SET QVD_EXISTS=0;  // false
                    END IF

                    //

                    if $(QVD_EXISTS) then
                    Concatenate (Products)
                    Load id, desc from test_qvd.qvd(qvd)
                    where not exists(id);
                    end if

                    store Products into $(qvd_file) (qvd);

                     

                      • Re: Concatenate Load from QVD file using Where Clause

                        Ok...here is where we are now:

                         

                         

                        SET ThousandSep='.';
                        SET DecimalSep=',';
                        SET MoneyThousandSep='.';
                        SET MoneyDecimalSep=',';
                        SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
                        SET TimeFormat='h:mm:ss TT';
                        SET DateFormat='D/M/YYYY';
                        SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
                        SET MonthNames='Ιαν;Φεβ;Μαρ;Απρ;Μαϊ;Ιουν;Ιουλ;Αυγ;Σεπ;Οκτ;Νοε;Δεκ';
                        SET DayNames='Δευ;Τρι;Τετ;Πεμ;Παρ;Σαβ;Κυρ';
                        
                        
                        Directory;
                        ODBC CONNECT TO [ITDEVELOP06-PC-Test] (XUserId is QKGTbZFMWD);
                        Products:
                        SQL SELECT id,  
                            name,
                            dt_create,
                            dt_update    
                        FROM Test.dbo.Products
                        ;
                        
                        
                        set qvd_file = test_qvd.qvd;
                        let fp = '$(QvWorkPath)';
                        let full = '$(fp)' & '\' & '$(qvd_file)';
                        let fs = FileSize('$(full)');
                        let qvd_exists = if($(fs)>0, -1, 0);
                        if $(qvd_exists) then
                         Concatenate (Products)
                         Load id, name, dt_create, dt_update from $(full)(qvd)
                         where not exists(id);
                        end if
                        store Products into $(qvd_file) (qvd);
                        
                        

                         

                        Thi is now CORRECT. In Summary:

                         

                        1) yes, i had to add (qvd) in LOAD and STORE

                        2) yes, i had to check the existence of file. However, this could only be checked with first building the full path to the file using QvWorkPath. Both

                        document qvw and data file qvd are on the same working dir.

                         

                        Anyway, thanks for your help. It is my first serious script with QlikView. I HAD VERY HARD TIME with that. Thinks like :

                         

                        FileSize($(full)) does not work, but it has to be :

                         

                        FileSize('$(full)') .....really drove me crazy till I find what is going wrong.

                         

                        BAD. Anyway. Thanks again....going now to where clause with last execute time.

                  • Re: Concatenate Load from QVD file using Where Clause
                    Rob Wunderlich

                    ath pointed out some problems, but you are also missing the (qvd) parameter in your From:

                     

                    FROM [flight_reservations.qvd] (qvd)

                     

                    -Rob

                    http://robwunderlich.com

                    • Re: Concatenate Load from QVD file using Where Clause
                      ALEXANDRE PERROT

                      Hi !

                      @Panayotis : are you working with GDS systems for travel agency like Sabre, WorldSpan or Apollo ? If yes, how do you connect QlikView to thoses data sources ? thx !