topic Re: Condition Reload in QlikView

Condition Reload

Thu, 03 Dec 2015 04:46:14 GMT

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

Susvith

Re: Condition Reload

Thu, 03 Dec 2015 05:36:28 GMT

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

Unable to put that logic in if condition

Re: Condition Reload

jagan — Thu, 03 Dec 2015 05:43:02 GMT

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;

Re: Condition Reload

Thu, 03 Dec 2015 06:52:45 GMT

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.

Susvith

Re: Condition Reload

jagan — Thu, 03 Dec 2015 07:43:49 GMT

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

Regards,

Jagan.

Re: Condition Reload

effinty2112 — Thu, 03 Dec 2015 15:06:54 GMT

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