Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If you don't already have it, you may want to download a copy of the QV Cookbook from
http://robwunderlich.com/downloads/
The Cookbook has a incremental reload example as well as a number of other useful script examples.
-Rob
Παναγιώτη
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
Διαμαντής
ath pointed out some problems, but you are also missing the (qvd) parameter in your From:
FROM [flight_reservations.qvd] (qvd)
-Rob
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.
@Rob Thanks for your answer. Yes, you are right about that. I have found this week point there. But still I have other problems with implementing a short incremental load example. See my reply to "ath_diamantis"
You need single quotes around the qvd_file variable.
if(FileSize('$(qvd_file)')...
-Rob
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);
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.
If you don't already have it, you may want to download a copy of the QV Cookbook from
http://robwunderlich.com/downloads/
The Cookbook has a incremental reload example as well as a number of other useful script examples.
-Rob
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 !