topic Re: How do you validate number of records imported against the data source record count? in QlikView

How do you validate number of records imported against the data source record count?

Anonymous — Tue, 17 Mar 2015 19:29:26 GMT

All,

I'm having some difficulty in validating the number of records I have imported into a .QVW against a count of the records from the data source.

Here is what I have so far:

RecordTotal:

LOAD

RowCtr;

SELECT COUNT(*) AS RowCtr

FROM "testdb".dbo."test_table";

ImportedRecords:

LOAD

FieldA,

FieldB,

FieldC,

FieldD;

SQL SELECT

FieldA,

FieldB,

FieldC,

FieldD

FROM "testdb".dbo."test_table";

Let vSQLRecordCt = RowCtr;

Let vQVRecordCt = NoOfRows('ImportedReocrds')

If $(vSQLRecordCt) = $(vQVRecordCt) Then

Set vRecordCtValidation = 'Imported vs data source record counts match - extract process complete.';

Else

Set vRecordCtValidation = 'Imported vs data source record counts do not match - Admin attention is required.';

End If

Trace $(vRecordCtValidation)

It seems that my code is having a problem debugging at the If.. Then.. statement. I have tried a bunch of different combinations to try to feed to a variable the SELECT COUNT(*) result, but can't seem to find the proper way to do it.

Thanks in advance for the feedback.

Re: How do you validate number of records imported against the data source record count?

Anonymous — Tue, 17 Mar 2015 19:36:17 GMT

Let vSQLRecordCt = peek('RowCtr');

Re: How do you validate number of records imported against the data source record count?

maxgro — Tue, 17 Mar 2015 19:44:33 GMT

this works, adapt to your requirement

RecordTotal:

load

100 as RowCtr

autogenerate 1;

ImportedRecords:

load

'aaa' as Dim1,

'bbb'as Dim2

autogenerate 100;

//autogenerate 99;

Let vSQLRecordCt = peek('RowCtr',0,'RecordTotal');

Let vQVRecordCt = NoOfRows('ImportedRecords');

if $(vSQLRecordCt) = $(vQVRecordCt) Then

Set vRecordCtValidation = 'Imported vs data source record counts match - extract process complete.';

Else

Set vRecordCtValidation = 'Imported vs data source record counts do not match - Admin attention is required.';

ENDIF;

Trace $(vRecordCtValidation);

Re: How do you validate number of records imported against the data source record count?

Anonymous — Thu, 19 Mar 2015 12:41:39 GMT

Thanks so much everyone. I had forgotten that I can't arbitrarily assign a variable to a fieldname, but need to use the Peek function to do so.