topic Re: Concatenate Load from QVD file using Where Clause in QlikView

Concatenate Load from QVD file using Where Clause

Thu, 01 Dec 2011 13:38:21 GMT

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

Thu, 01 Dec 2011 14:22:15 GMT

Παναγιώτη

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

rwunderlich — Thu, 01 Dec 2011 14:37:21 GMT

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

Thu, 01 Dec 2011 14:37:41 GMT

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

Thu, 01 Dec 2011 14:39:38 GMT

@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"

Re: Concatenate Load from QVD file using Where Clause

rwunderlich — Thu, 01 Dec 2011 15:35:37 GMT

You need single quotes around the qvd_file variable.

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

-Rob

Re: Concatenate Load from QVD file using Where Clause

Thu, 01 Dec 2011 15:37:35 GMT

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

Thu, 01 Dec 2011 15:54:38 GMT

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

rwunderlich — Thu, 01 Dec 2011 18:28:08 GMT

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

http://robwunderlich.com

Re: Concatenate Load from QVD file using Where Clause

Anonymous — Mon, 21 Jan 2013 20:51:50 GMT

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 !