Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anuhyak1
Creator
Creator

Convert fields of two tables into upper case and Join them to get into single table

Hi,

I'm reading data from two excel files by using a loop. One Excel has 72 fields and other excel has 76 fields .

Problem:

1. case of column names is inconsistent so while concatenating data from each file it creating multiple columns.

Ex: Country(from first xls) , counTry(from second xls) is bought into 2 different columns instead of one due to case inconsistent in xls files.

1. Can we  convert all the fields from both tables to Upper case irrespective of the data  and 

2. Join them into a single table.

could you please help me with the syntax?

Attached Excel files for your reference.

 

 

 

 

 

Labels (4)
1 Reply
danielrozental
Master II
Master II

You can try something like this.

 

Uppercase:
Load null() as null
AutoGenerate 0;


for each vFile in filelist('*.xlsx')
Table:
LOAD *
FROM $(vFile)
(ooxml, embedded labels, table is Sheet1);

Let sLoad = '';

for i = 1 to NoOfFields('Table')
let sField = FieldName(i,'Table');
if sLoad <> '' then
let sLoad = sLoad & ',';
end if
let sLoad = sLoad & '[' & sField & '] AS [' & upper(sField) & ']';

next
drop table Table;

Concatenate(Uppercase)
Load $(sLoad)
FROM $(vFile)
(ooxml, embedded labels, table is Sheet1);

next
drop field null;