Checking source file for correct field names before load

[#1]

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.

[#2]

You might require single quotes around your table names.

Try:

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

[jagan]

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.

[#4]

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?

[jagan · Accepted solution]

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.

[#6]

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

[jagan]

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.