topic Re: Checking source file for correct field names before load in Qlik Learning Discussions

Checking source file for correct field names before load

Wed, 29 Oct 2014 05:09:09 GMT

Hi I am have a check on a source file - if it has all the fields with the correct names, instead of the error window popping out I want to custom handle it. I have an existing QVD file so I am trying to match the field names of the source file with the ones existing in the QVD one by one and in the end record all the fields that are missing or have wrong names.

For this I am trying to use a For Each loop, which errors out :

For i = 0 TO NoOfFields('QVDTable') - 1

IF FieldName($(i), Table) = FieldName($(i), QVDTable) THEN

LET vFieldCount = 0;

ELSE

LET vFieldCount = 1;

ENDIF

NEXT i

[Table] is loaded from the source file and [QVDTable] is loaded from the QVD.

Please help.

Re: Checking source file for correct field names before load

Wed, 29 Oct 2014 05:58:36 GMT

You might require single quotes around your table names.

Try:

IF FieldName($(i), 'Table') = FieldName($(i), 'QVDTable') THEN

Re: Checking source file for correct field names before load

jagan — Wed, 29 Oct 2014 06:08:48 GMT

Hi,

Try this sample script

Table:

LOAD

INLINE [

A,B,C

1,2,3];

QVDTable:

LOAD

INLINE [

A,B,D

1,2,4];

LET vFieldName = '';

LET vQVDTableFieldName = FieldName(3, 'Table');

For i = 1 TO NoOfFields('QVDTable')

LET vTableFieldName = FieldName($(i), 'Table');

LET vQVDTableFieldName = FieldName($(i), 'QVDTable');

IF vTableFieldName = vQVDTableFieldName THEN

//DO Nothing

ELSE

LET vFieldName = vFieldName & vQVDTableFieldName & ';';

ENDIF

NEXT i

Regards,

Jagan.

Re: Checking source file for correct field names before load

Wed, 29 Oct 2014 06:35:57 GMT

Hi Jagan,

Thanks for the reply. This works but creates a problem when there's a missing field. All the following field names comes as a no-match, I want to take care of all the cases - missing fields, wrong field names, extra fields, How do I do that, could you please suggest?

Re: Checking source file for correct field names before load

jagan — Wed, 29 Oct 2014 07:08:28 GMT

Hi,

Try this script for getting Missing and Additional fields, it is difficult to identify the wrong field names.

QVDTable:

LOAD

INLINE [

A,B,D

1,2,4];

Table:

LOAD

INLINE [

A,B,C

1,2,3];

LET vMissingFieldNames = 'Missing Field Names in Table : ';

LET vAdditionalFieldNamesInTable = 'Additional Field Names in Table : ';

For i = 1 TO NoOfFields('QVDTable')

LET vQVDTableFieldName = FieldName($(i), 'QVDTable');

IF Alt(FieldNumber(vQVDTableFieldName, 'Table'), 0) = 0 THEN

LET vMissingFieldNames = vMissingFieldNames & vQVDTableFieldName & ';';

ENDIF

NEXT i

For i = 1 TO NoOfFields('Table')

LET vTableFieldName = FieldName($(i), 'Table');

IF Alt(FieldNumber(vTableFieldName, 'QVDTable'), 0) = 0 THEN

LET vAdditionalFieldNamesInTable = vAdditionalFieldNamesInTable & vTableFieldName & ';';

ENDIF

NEXT i

Hope this helps you.

Regards,

Jagan.

Re: Checking source file for correct field names before load

Wed, 29 Oct 2014 11:08:02 GMT

Thanks Jagan, works perfectly. Also, I used three if conditions while comparing the records in both the tables so all the cases are implemented

Re: Checking source file for correct field names before load

jagan — Thu, 30 Oct 2014 01:51:13 GMT

Hi,

Please close this thread by giving Correct and Useful Answers to the post which helps you in finding the solution, it helps others in finding the answers easily for similar scenarios.

Regards,

Jagan.