Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Checking source file for correct field names before load

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Checking source file for correct field names before load

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 Replies
Not applicable

Re: Checking source file for correct field names before load

You might require single quotes around your table names.

Try:

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

MVP
MVP

Re: Checking source file for correct field names before load

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.

Not applicable

Re: Checking source file for correct field names before load

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?

MVP
MVP

Re: Checking source file for correct field names before load

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.

Not applicable

Re: Checking source file for correct field names before load

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

MVP
MVP

Re: Checking source file for correct field names before load

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.

Community Browser