topic Re: How do i find missing Fields? in QlikView

How do i find missing Fields?

Wed, 25 Nov 2020 16:16:04 GMT

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 !!

Re: How do i find missing Fields?

its_anandrjs — Tue, 08 Aug 2017 07:25:47 GMT

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

Re: How do i find missing Fields?

Chanty4u — Tue, 08 Aug 2017 07:27:47 GMT

Hi check here,

Re: How do i find missing Fields?

Tue, 08 Aug 2017 07:33:31 GMT

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

Re: How do i find missing Fields?

Tue, 08 Aug 2017 07:36:37 GMT

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.

Re: How do i find missing Fields?

Chanty4u — Tue, 08 Aug 2017 07:39:29 GMT

yes, Please read this too it may help you

Re: Header lines in an Excel file to LOAD

Re: How do i find missing Fields?

balar025 — Tue, 08 Aug 2017 08:19:28 GMT

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?

Re: How do i find missing Fields?

Miguel_Angel_Baeyens — Tue, 08 Aug 2017 08:42:52 GMT

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.

Re: How do i find missing Fields?

balar025 — Tue, 08 Aug 2017 08:47:40 GMT

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;

Re: How do i find missing Fields?

Miguel_Angel_Baeyens — Tue, 08 Aug 2017 08:49:52 GMT

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 ?)

Re: How do i find missing Fields?

Tue, 08 Aug 2017 09:29:56 GMT

We can check with previously loaded table. following table is the original table which I have already loaded in Qlikview. new excel has to be same as like previous one. but sometime columns may get changed. in that case I need these check points

Data:

LOAD

Date,

Source_No,

EMP_ID,

CountryID,

Level

FROM

[$(vSource_Data)\Data*.csv]

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

Re: How do i find missing Fields?

balar025 — Tue, 08 Aug 2017 09:52:56 GMT

You can use above script and scripterrormode variable at time and have list of mismatch in other table with tablename.

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;

Re: How do i find missing Fields?

Tue, 08 Aug 2017 11:21:11 GMT

I see your format is correct and logical

How can I store information in .txt format for mismatch fields in certain table in above code?

can you just tell me where I need to modify ?

Re: How do i find missing Fields?

balar025 — Wed, 09 Aug 2017 04:53:26 GMT

You just need to store Table into txt as below.

Give table name

MisMatch:

Load

'$(vFieldName)' as MisMatch_Field,

'$(vTableName)' as MisMatch_TABLE_NAME

AutoGenerate(1);

and last statement after next i;

Store MisMatch into MisMatch.txt(txt);

Let me know in case of any issue.