topic Compare Records in QlikView

Compare Records

Tue, 01 Dec 2015 15:54:23 GMT

I got a requirement which I never worked earlier. I have a total 4 QVW's which are PLD1000, PLD1100, PLD1200 and PLD1300.

PLD1000 is the the parent qvw and rest are children. Due to heavy load of 45 million records in PLD1000, the records has been divided into PLD1100, PLD1200 and PLD1300 as per requirements. Which means sum of record count in PLD1100+PLD1200+PLD1300 = PLD1000.

The problem here is, we observed that some unwanted records are also getting fetched in children qvw's. The sum of records is not validating. So the task is here, to compare the records in each qvw and if it is not matched the load should fail and should show the reason in the log file as " count of records not matched".

The idea I am trying to execute is to insert the main table without any transformations in one of the child file with the respective business logic. And I am trying to write IF condition to validate the records and fail the load process if it is not matching and show it in log file. I got struck in writing this condition. Can anyone could please help me out? Any other ideas to achieve this are also appreciated.

Lets assume main table to be inserted as

Main_Data:

Now the number of records from this table should match with whole extracted records.

PS: Sorry for a long post. I tried to be brief as much as possible.

Thanks in Advance.

Susvith

Re: Compare Records

jagan — Thu, 03 Dec 2015 04:02:11 GMT

Hi,

Get the rows count of the table by using NoOfRows() in the script, and compare if it is not matching then throw and error like below

Data:

LOAD

FROm TableName;

If NoOfRows('TableName') <> SomeNumber THEN

TRACE count of records not matched // Trace logs the given text in log file.

DROP TABLE Dummy; // To raise an error we are dropping a table that doesn't exists

END IF

Hope this helps you.

Regards,

jagan.

Re: Compare Records

Thu, 03 Dec 2015 05:01:42 GMT

Hi jagan

Thanks for you reply.

I tried working as you said above, but its giving some syntax error.

I managed to keep this post more easier at

Condition Reload

Can you please check it for me?

Thanks in advance.

BR
Susvith

Re: Compare Records

jonathandienst — Thu, 03 Dec 2015 05:10:57 GMT

Compare the NoOfRows('TableName') With QvdNoOfRecords('MyFile.qvd')...

Re: Compare Records

Thu, 03 Dec 2015 05:14:45 GMT

Thanks for your reply.

I had tried writing it.

But I got failed in writing the if condition reload.

Can you please help me in it. For sample files Condition Reload

Thanks in Advance

Susvith

Re: Compare Records

jagan — Thu, 03 Dec 2015 05:43:12 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;

Regards,

Jagan.