How do i find missing Fields?

[#1]

Hi Folks,

I'm getting data from Excel like following and after every two days excel will replaced by new same excel same table. But for checkpoint purpose I'll find that whether I'm getting new fields in new data or some fields are missing as compared to old table.

Data:

LOAD

     Date,

     Source_No,

     EMP_ID,

     CountryID,

     Level

FROM

[$(vSource_Data)\Data*.csv]

(txt, codepage is 1235, embedded labels, delimiter is ',', msq);

How do I catch the difference?

AND if the table is same as compared to old one i'll move this source Excel on another location.

Will appreciate help !!

[its_anandrjs]

Hi,

You can try with Load * From Location;

Data:

LOAD

*

FROM

[$(vSource_Data)\Data*.csv]

(txt, codepage is 1235, embedded labels, delimiter is ',', msq);

in this case you get full load with all fields on the excel file.

Regards

Anand

[Chanty4u]

Hi check here,

[#4]

I think this will not work. How do I find missing fields through * ?

* will load all fields. Suppose in next excel EMP_ID becomes ID then how this * will help me

[#5]

whether I'm getting new fields in new data or some fields are missing as compared to old table.

Suppose instead of 5 fields, new excel has only 4 fields. then * will not work anymore.

[Chanty4u]

yes,   Please read this too it may help you

Re: Header lines in an Excel file to LOAD

[balar025]

Hi Mohan,

any base table to with you are matching ? By mean you compare with one table right which is previously loaded?

If multiple file is having mismatch of column. In that case?

[Miguel_Angel_Baeyens]

You need to loop through the list of files, then through the list of fields. This could make the loading process very slow, but it works:

For Each File In FileList (Root & '\*') 

  FileTemp:
  LOAD *
  FROM $(File)
  (parameters here);

  SET vFileName = FieldBaseName();

  For f = 1 to NoOfFields('FileTemp')

    File_$(vFileName):
    LOAD
    FieldName($(f),'FileTemp') as FieldName // add your controls to modify fieldnames here
    AutoGenerate 1;

  Next f

  DROP TABLE FileTemp;

Next File

I wrote it from memory so check for missing parentheses or semicolons.

[balar025]

Below script might help you to find out mismatch with reference to old table.

Let vTableName = TableName(0);

Let vNoOfColumns = NoOfFields('$(vTableName)');

Trace $(vTableName);

Let vFieldList = '';

for i = 1 to $(vNoOfColumns)

vFieldList = vFieldList&','&chr(39)&FieldName($(i),'$(vTableName)')&chr(39);

next i;

vFieldList = right((vFieldList),len(vFieldList)-1);

Trace $(vFieldList);

for i = 1 to $(vNoOfTable)-1

Let vTableName = TableName($(i));

Let vNoOfColumns = NoOfFields('$(vTableName)');

Trace $(vNoOfColumns);

for j=1 to $(vNoOfColumns)

vFieldName =  FieldName($(j),'$(vTableName)');

vFieldCheck = WildMatch('$(vFieldName)',$(vFieldList));

if $(vFieldCheck) = 0 then

Load

'$(vFieldName)' as MisMatch_Field,

'$(vTableName)' as MisMatch_TABLE_NAME

AutoGenerate(1);

ENDIF 

Trace $(vTableName) -> $(vFieldName);

next j;

next i;

[Miguel_Angel_Baeyens]

Or if you know the names already, and not need to do it dynamically, use

FieldNamesMap:

MAPPING LOAD

OldName,

NewName

FROM FileWithListOfFieldNames;

And after the tables have been loaded use

RENAME FIELDS USING FieldNamesMap;

See

How to rename a field in qlikview

Rename fields of a table (with applymap function ?)