Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
agdgn123
Not applicable

concatenate columns by field name and add fields by file name (if possible)

I have multiple excel files for students grade scores in various subjects from various schools and various periods . Here is an example for one of the excel files. The names in the grade scores columns are teacher names.

The subject and level are in the column titles (Math 3, Math 4 etc)

File name: ABC School Period 1 Year 2016

        

StudentIDPeriodClassJohn Smith math 3John Doe math 3Karen El Math 4Jane Doe Math 4Final Math
11A170 70
21A2 80 75
31A4 100
41A3 9595

I need to concatenate all tables into one table in the following format:

          

StudentIDSchoolNameYearPeriodClassSubjectLevelscoreFinal Math ScoreFinal Math?
1ABC20161A1MathMath 37070yes
2ABC20161A2MathMath 38075yes
3ABC20161A4MathMath 4100 no
4ABC20161A3MathMath 49595yes

my challenges:

1. Is it possible to bring the school name and year from the file name into a table field?

2. how do I concatenate the score fields based on a partial string in the column header name? (i don't care who is the teacher, i just care about the level (Math 3, Math 4 etc).

3. The amount of teachers and levels vary from school to school. is there a way to write one general script that will be able to handle all the files?

3 Replies
OmarBenSalem
Not applicable

Re: concatenate columns by field name and add fields by file name (if possible)

Hi anat,

Yes, it's possible.
I have a queston though, are your StudentIDs the same in each excel file? (1,2,3n4 and so?) or each student will have his unique name?

1) Let's assume your Excel files are all under the same folder/path and that all the titles are in the same format:

ABC School Period 1 Year 2016 (1 is school name, 6th word in the year)

       

here's is how we do to bring up the title and the year:

let vDir='D:\ExcelFiles\'; //your directory

LET vF=;


for each vF in filelist (vDir & '\*.xls' )

   let SchoolName= SubField('$(vF)', ' ',1)  ;

   let vSchoolName= '$(SchoolName)' ;

   let Year=SubField('$(vF)', ' ',6);

let vYear='$(Year)' ;

 

It begins as follow..
Can you please, provide me 2 excel samples from 2 schools, to work with and continue with my script?

Thanks a lot

ahaahaaha
Not applicable

Re: concatenate columns by field name and add fields by file name (if possible)

Hi Anat,

The idea of the solution can be seen in the attached file QVF. Next, you should take account the real names of your files of Excel and the paths to them in the file system.

Regards,

Andrey

agdgn123
Not applicable

Re: concatenate columns by field name and add fields by file name (if possible)

Thanks Omar!

The StudentID is unique (it's their Social Security Number).

I attached an example with 4 sheets representing the excel data i receive .

Each school sends a separate sheet for each period (1, 2), and each class grade (A, B, C) = a total of 6 spreadsheets per school per year.

Students can change teachers between semesters (See studentID 3)