topic Re: Extract the Missing or Mismatch Fields from Table Structure in QlikView

Extract the Missing or Mismatch Fields from Table Structure

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm trying to compare the file structure with required structure and trying to extract the missing fields and stored in .txt file.

I'm working on following script but I'm not getting the exact result.

Can someone help me through this?

Thanks in advance

FOR Each FoundFile in filelist('$(vSource)'&'\Data_level_*.csv')

LET vFileName = subfield(FoundFile,'\',-1);
LET vReportingDate = mid('$(vFileName)', 18, 6);

[Data]:
LOAD Date,
Engine,
Code,
CodeName,
Level
FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Next FoundFile;

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 = NoOfTables() - 1 to 0 step - 1
//for i = 1 to NoOfTables()-1
Let vTableName = TableName($(i));
Let vNoOfColumns = NoOfFields('$(vTableName)');
//Next i;
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: Extract the Missing or Mismatch Fields from Table Structure

Anil_Babu_Samineni — Wed, 16 Aug 2017 08:32:47 GMT

I'm working on following script but I'm not getting the exact result.

May be explore this part?

Re: Extract the Missing or Mismatch Fields from Table Structure

Anil_Babu_Samineni — Wed, 16 Aug 2017 08:33:46 GMT

And this part may be you need

LET vFileName = subfield(FoundFile,'\',-1);

FoundFile should be in '$(FoundFile)'

Re: Extract the Missing or Mismatch Fields from Table Structure

Wed, 16 Aug 2017 08:43:44 GMT

Yes but result won't appear. I'm trying to extract the information for mismatch fields in table.

Re: Extract the Missing or Mismatch Fields from Table Structure

Anil_Babu_Samineni — Wed, 16 Aug 2017 08:52:40 GMT

May be try this? If not, I would ask you to share sample CSV files (Atleast 2 files)

SET vSource = D:\Prod\Tranformation;

FOR Each FoundFile in filelist('$(vSource)'&'\Data_level_*.csv')

LET vFileName = subfield('$(FoundFile)','\',-1);

LET vReportingDate = mid('$(vFileName)', 18, 6);

[Data]:

LOAD Date,

Engine,

Code,

CodeName,

Level

FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Next FoundFile;

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 = NoOfTables() - 1 to 0 step - 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);

END IF

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

next j;

next i;

Re: Extract the Missing or Mismatch Fields from Table Structure

Thu, 17 Aug 2017 06:04:33 GMT

Hi Anil,

Its not working still. can you have any solution on that ?