Thats great guys thanks for the replies. i dont think it will work tho as there are around 200 fields, and within the fieldnames of these 200 fields it will include the 'Term' (autumn, Summer ect). so i will need the new 'Term' field to look at all of these 200 fields.
The inline load was just an example. When you add it to your script and use the equation against whatever your field name is, it will look at every field, no matter how many you have. If you have a sample file, it would be easier for Diego and I to show you what we mean.
Seasons: LOAD *, If(WildMatch(Title,'*Autumn*'),'Autumn', If(WildMatch(Title,'*Spring*'),'Spring', If(WildMatch(Title,'*Summer*'),'Summer', If(WildMatch(Title,'*Winter*'),'Winter','None')))) as Term Resident Data; DROP Table Data;
the lines PupilResultsY1SpringReadingAPS,etc are field names not values within a field. so where you have the field name of Title, and then the data under it. its the data under it hat are my field names.
I see what you mean. Unfortunately that makes it a lot harder. I have attached an example in which I used conditional layouts to show the relevant column headers based on season selection. However, this may not be feasible for you if you have so many. Maybe someone else on the forum will have a better solution?