topic Avoid Qvd if not present in script?? in QlikView

Avoid Qvd if not present in script??

sunil1989 — Tue, 11 Apr 2017 07:32:44 GMT

Hi ,

Thanks in advance.

I have three QVD of three tables i.e.orders, payment and refunds.

And in my main Transformation qvd i am doing full outer join of this three table.

orders

join

payment

join

Refund

suppose if payment qvd vice versa is not presnt then script get failed , my requirement is if payment qvd is not presnt then also script should run with

orders

join

Payment.

Thanks,

Sunilkumar

Re: Avoid Qvd if not present in script??

ahaahaaha — Tue, 11 Apr 2017 07:41:39 GMT

Hi Sunilkumar,

Can we see the source data fragment and the expected result?

Regards,

Andrey

Re: Avoid Qvd if not present in script??

MK_QSL — Tue, 11 Apr 2017 07:47:59 GMT

Add below variable before at the script start.

SET ErrorMode =0;

Re: Avoid Qvd if not present in script??

asgardd2 — Tue, 11 Apr 2017 07:59:44 GMT

Hello!

I think you can check availability files before join.

Re: Avoid Qvd if not present in script??

Miguel_Angel_Baeyens — Tue, 11 Apr 2017 08:14:26 GMT

Also using file and system functions before doing the join like

IF FileSize('Orders.qvd') THEN
  TRACE Order QVD is missing; // the script log file will contain this line so you know which file was missing
  SET vOrderFile = 0;
ELSEIF FileSize('Payments.qvd') THEN
  TRACE Payment QVD is missing;
  SET vPaymentFile = 0;
ELSEIF FileSize('Refunds.qvd') THEN
  TRACE Refund QVD is missing;
  SET vRefundFile = 0;
END IF

LET vAllFiles = $(vOrderFile) + $(vPaymentFile) + $(vRefundFile);

IF vAllFiles = 3 THEN

// Script with JOINs goes here

END IF

Also, see some different options here: count files in folder

Re: Avoid Qvd if not present in script??

sunil1989 — Tue, 11 Apr 2017 08:21:09 GMT

Orders:

LOAD

orderId,

actualDeliveryDateTime,

customerId,

num#(fulfillmentSiteId,'#') as fulfillmentSiteId,

orderCurrency,

finalTotalItemsUndiscountedAmount,

finalTotalItemsDiscountedAmount,

finalTotalChargesUndiscountedAmount,

finalTotalChargesDiscountedAmount,

finalTotalFinalAmount,

deliveryChargeUndiscountedAmount,

deliveryChargeDiscountedAmount,

orderPromotionId,

orderDscntAmt,

salesDate,

salesDateKey,

loadDate as ord_lastUpdtDate,

//loadDate,

loadDateKey as ord_lastUpdtKey,

1 as flagOrders

from [$(vExtractBigQueryFactQVDPath)Orders.qvd](qvd)

;

JOIN

RefundsAggr:

LOAD

orderId,

customerId as Refund_customerId,

refundAuthorizedDateTime,

finalRefundTotalAmount,

loadDateKey as Refund_lastUpdtKey,

1 as flagRefunds

from [$(vTransformQVDPath)RefundsAggr.QVD](qvd)

;

JOIN

PaymentsAggr:

LOAD

orderId,

paymentServiceProvider,

paymentCompletionDateTime,

transactionStatus,

loadDate as Pay_lastUpdtDate,

fundingInstrument,

finalTotalAmount,

PaymentRepresents,

paymentDtKey,

1 as flagPayments

FROM

[$(vTransformQVDPath)PaymentsAggr.qvd]

(qvd)

;

Re: Avoid Qvd if not present in script??

sunil1989 — Tue, 11 Apr 2017 08:24:54 GMT

Suppose any of the above qvd is not present then also my script should run.