Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions

Re: Concatenate Load from QVD file using Where Clause

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

9 Replies
Not applicable

Re: Concatenate Load from QVD file using Where Clause

Παναγιώτη

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

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

Not applicable

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.

Not applicable

Re: Concatenate Load from QVD file using Where Clause

@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

You need single quotes around the qvd_file variable.

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

-Rob

Not applicable

Re: Concatenate Load from QVD file using Where Clause

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);

Not applicable

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

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

alexandre_perro
New Contributor

Re: Concatenate Load from QVD file using Where Clause

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 !

Community Browser