topic Re: Columns Exist or Not in QlikView

Columns Exist or Not

sspawar88 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Columns Exist or Not

ahaahaaha — Tue, 01 Aug 2017 07:25:43 GMT

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

Re: Columns Exist or Not

prat1507 — Tue, 01 Aug 2017 07:47:16 GMT

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

Re: Columns Exist or Not

marcus_malinow — Tue, 01 Aug 2017 08:05:44 GMT

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

Re: Columns Exist or Not

sspawar88 — Tue, 01 Aug 2017 08:18:19 GMT

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"

Re: Columns Exist or Not

marcus_malinow — Tue, 01 Aug 2017 08:21:06 GMT

Of course.

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

Re: Columns Exist or Not

sspawar88 — Tue, 01 Aug 2017 08:44:32 GMT

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 ?

Re: Columns Exist or Not

marcus_malinow — Tue, 01 Aug 2017 08:50:22 GMT

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

Re: Columns Exist or Not

sspawar88 — Tue, 01 Aug 2017 09:10:07 GMT

Lol

Thank you !!

Re: Columns Exist or Not

effinty2112 — Tue, 01 Aug 2017 10:25:33 GMT

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