Condition Reload

[#1]

Hi,

I need to reload based upon 2 Conditions.

1) No. of qvd records = No.ofrows(Tablename)

2) Sum of Qvd.Field1+Qvd.Field2 = Sum Table.field1= Table.Field2

I am able to load the values in variable. I got struck at finishing the If conditional statement.

Can someone please help me.

Any help is appreciated

BR

Susvith

[#2]

I have idea of comparing records number in between table rows and qvd records.

Unable to put that logic in if condition

[jagan]

HI,

Try like this

Temp1:

LOAD

     Sum(UnitCost + UnitPrice) as Sum1

FROM

Products.qvd

(qvd);

NoConcatenate

Test:

LOAD CategoryID,

     Pricegroup1,

     ProductID,

     ProductName,

     QuantityPerUnit,

     SupplierID,

     UnitCost,

     UnitPrice,

     UnitsInStock,

     UnitsOnOrder

FROM

Nikhil.xls

(biff, embedded labels, table is Sheet1$);

Temp2:

LOAD

     Sum(UnitCost + UnitPrice) AS Sum2   

FROM

Nikhil.xls

(biff, embedded labels, table is Sheet1$);

Let TableCount= NoOfRows('Test');

Let QvdCount= QvdNoOfRecords('Products.qvd');

Let vQVDSum= Peek('Sum1', 0, 'Temp1');

Let vExcelSum= Peek('Sum2', 0, 'Temp2');

IF TableCount <> QvdCount THEN

  TRACE "Records count not matched";

  DROP TABLE TEst007;

ELSEIF vQVDSum - vExcelSum > 0 THEN

  TRACE Sum not matched;

  DROP TABLE TEst007;

ENDIF

Drop Table Temp1, Temp2;

[#4]

Hi jagan,

I Tried the whole process which you mentioned above. But unfortunately I Couldn't able to achieve it.

If Count of records doesn't match and sum of values doesn't match then it fails the job by showing error drop table doesn't found. IF only one of the error exist then this wont fail the job but just drop the table.

IF any one of the condition doesn't match it should fail the job.

Any clues on this.

Thanks in advance jagan.

BR

Susvith

[jagan]

The code works like that only, if any one condition fails it will throw error and logs corresponding message in log file.

Regards,

Jagan.

[effinty2112]

Hi Susvith,

This script might suggest something to you, it's just a scrap you can maybe use. It makes a hash of these fields in each table:

ProductID, QuantityPerUnit, SupplierID, UnitCost, UnitPrice, UnitsInStock, UnitsOnOrder

You can alter this list to give you a key you'll be confident will be an adequate test. You can replace any field with a sum of fields if you wish.

XLS_Products:

Load

Hash128(ProductID & '|' & QuantityPerUnit & '|' & SupplierID & '|' &

UnitCost & '|' & UnitPrice & '|' & UnitsInStock & '|' & UnitsOnOrder) as LineHashID

FROM

Nikhil.xls

(biff, embedded labels, table is Sheet1$);

Let vXLSRecordCount = NoOfRows('XLS_Products');

TRACE;

TRACE xls record count : $(vXLSRecordCount);

TRACE;

NoConcatenate

TempQVDProducts:

LOAD * FROM Products.qvd (qvd);

Let vQVDRecordCount = NoOfRows('TempQVDProducts');

TRACE;

TRACE qvd record count : $(vQVDRecordCount);

TRACE;

DROP table TempQVDProducts;

Left Keep(XLS_Products)

QVD_Products:

Load

Hash128(ProductID & '|' & QuantityPerUnit & '|' & SupplierID & '|' &

UnitCost & '|' & UnitPrice & '|' & UnitsInStock & '|' & UnitsOnOrder) as LineHashID

FROM

Products.qvd

(qvd);

Let vQVDMatchingRecordCount = NoOfRows('QVD_Products');

TRACE;

TRACE qvd matching record count : $(vQVDMatchingRecordCount);

TRACE;

if $(vXLSRecordCount) = $(vQVDRecordCount) then

TRACE QVD has same number of records as XLS;

ELSE

TRACE QVD DOES HAVE SAME NUMBER OF RECORDS AS XLS;

ENDIF

if $(vXLSRecordCount) = $(vQVDMatchingRecordCount) then

TRACE QVD matches XLS;

ELSE

TRACE QVD DOES NOT MATCH XLS;

ENDIF