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: 
danm84306
Contributor III
Contributor III

Load multiple fields with different name into one.

Hello,

            I have an issue. I am trying to load
csv file data from a folder. The 30+csv files have 7 different names for the
same data. I want to load all files (*.csv) as 1 field. Example below. (not all
files will have all names listed. Each will have only 1 variation)

     stunum as Student_Id,

     Id as Student_Id,

     Stunum as Student_Id,

     StuNum as Student_Id,

     ID as Student_Id,

     PrintID as Student_Id,

     id as Student_Id,

6 Replies
JonnyPoole
Employee
Employee

i was thinking the field names may not be important as you can use the @ sign to bring in the first field.

Just make sure you specify if there are leading rows to ignore.

LOAD

  @1 as ID

FROM

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

attached are the 2 CSV files i tested with.

danm84306
Contributor III
Contributor III
Author

Hello,

      This wil not work. the field is in a different column on each file.

JonnyPoole
Employee
Employee

K.  I'll try dig around for some code that will read the field headers to see whats there and then pull only the relevant field for each file.

simenkg
Specialist
Specialist

how about:

rangemax(stunum, Id, Sunum, StuNum, ID, PrintID, id) as Student_Id

simenkg
Specialist
Specialist

To give you a full solution you can do something like this:

Table1:

Load * from path\*.csv etc.....

Table2:

load *,

rangemax(stunum, Id, Sunum, StuNum, ID, PrintID, id) as Student_Id

resident Table1;


drop table Table1;

Drop fields stunum, Id, Sunum, StuNum, ID, PrintID, id;

Anonymous
Not applicable

or how about the following:

use the following statement after your load statement which reads fields and data from the csv file.

rename fields using Mapping_Rename_Table;

where Mapping_Rename_Table is defined (has to be before the load statement from csv file) as

Mapping_Rename_Table:

mapping load * inline[

%Oldname, %Newname

stunum, Student_Id

Id, Student_Id

Stunum, Student_Id

StuNum, Student_Id

ID, Student_Id

PrintID, Student_Id

id, Student_Id

];

this way you can actually load all the files and not worry about which csv has which variation of student_id field name. in fact, you can add any other fields that you want to rename to the list.

replace % with whatever hide prefix you set. if there isn't one already set, you can set it using the following statement after the standard set statements in the Main script tab

Set hideprefix = '%';