Avoid Qvd if not present in script??

[sunil1989]

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

[ahaahaaha]

Hi Sunilkumar,

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

Regards,

Andrey

[MK_QSL]

Add below variable before at the script start.

SET ErrorMode =0;

[asgardd2]

Hello!

I think you can check availability files before join.

[Miguel_Angel_Baeyens]

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

[sunil1989]

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)

;

[sunil1989]

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