Columns Exist or Not

[sspawar88]

Hi All,

How to check whether same columns exist in new table. Suppose I have excel which holds following columns

ID	Name	Location
1	A	IN
2	B	US

and suddenly I got new excel in which these columns are changed. I need to raise error like '' table are not in structure" in log file

Thanks in Advance

[ahaahaaha]

Hi,

May be like this?

set ErrorMode=0;

load * from abc.qvw;

if ScriptError=11 then

exit script;

//no no field;

end if

Regards,

Andrey

[prat1507]

Maybe something like this:

Table1:

LOAD * Inline [

A, B

1, 2

];

Let vrow=NoOfRows(Table1);

Table2:

LOAD * from data.csv;

NoConcatenate

Table3:

LOAD * from data.csv;

Let vrow1=NoOfRows(Table1);

Let vrow2=NoOfRows(Table3);

SET vVariable= if($(vrow1)=$(vrow)+$(vrow2),'table are in structure','table are not in structure');

Table: 

LOAD '$(vVariable)' as [Variable Value] 

AutoGenerate 1; 

STORE [Variable Value] FROM Table INTO YourFile.txt (txt); 

[marcus_malinow]

Something like this - substitute in your own files:

FirstTable:
LOAD *
FROM
[File 1]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LET vFirstTableFieldList = '';
for n = 1 to NoOfFields('FirstTable')
Let vFirstTableField = FieldName(n, 'FirstTable');
TRACE $(vFirstTableField);

if n = 1 then
LET vFirstTableFieldList = '$(vFirstTableField)';
else  
LET vFirstTableFieldList = '$(vFirstTableFieldList), $(vFirstTableField)';
end if
next n

drop Table FirstTable;

SecondTable:
NOCONCATENATE
LOAD *
FROM
[File 2]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

LET vSecondTableFieldList = '';
for n = 1 to NoOfFields('SecondTable')
Let vSecondTableField = FieldName(n, 'SecondTable');
TRACE $(SecondTableField);

if n = 1 then
LET vSecondTableFieldList = '$(vSecondTableField)';
else  
LET vSecondTableFieldList = '$(vSecondTableFieldList), $(vSecondTableField)';
end if
next n

drop Table SecondTable;

if '$(vFirstTableFieldList)' = '$(vSecondTableFieldList)' then
TRACE fields are the same;
else
TRACE fields are different;
end if

[sspawar88]

rather than loading both table can we load just first one and compare new file while loading from existing script.

if it match with existing script then " Table structure is Same"

if it does not match then "Table Structure is Different"

[marcus_malinow]

Of course.

Drop the first load, and just set vFirstTableFieldList to your expected field list (separated with a comma and space)

[sspawar88]

There are number of fields in my table. do I need to put each field in field list?

can we have some optimize way to do this ?

[marcus_malinow]

Satish,

No need to do that. Qlik will magically determine what columns you are looking for by reading your mind.

Seriously? Either you define your field list, or you load a reference file in the first table load. You have enough information to be able to do this.

Marcus

[sspawar88]

Lol

Thank you !!

[effinty2112]

Hi Satish,

Before loading the data from your excel file load some metadata first using ODBC.

Let vXLSPath='PathIncludingFileNameToFirstFile.xlsx';

ODBC CONNECT TO [Excel Files;DBQ=$(vXLSPath)];

TempMetaData:

     SQLCOLUMNS;

     DISCONNECT;

Columns:

Load

1 as File1,

COLUMN_NAME as Field

Resident TempMetaData;

DROP Table TempMetaData;

Let vXLSPath='PathIncludingFileNameToSecondFile.xlsx';

ODBC CONNECT TO [Excel Files;DBQ=$(vXLSPath)];

TempMetaData:

     SQLCOLUMNS;

     DISCONNECT;

Outer Join (Columns)

Load

1 as File2,

COLUMN_NAME as Field

Resident TempMetaData;

DROP Table TempMetaData;

now a simple tablebox with columns Field, File1, File2 will show you where there is a difference between the fields.

cheers

Andrew