How to transform excel pivot into something useful!

[Dayna]

Hello,

I have a excel spreadsheet that will likely grow with more people / courses which I'd like to report on. It's currently in a pivot style formatting (see attached).

I think I need to split them out into two or three tables, but not sure how... What I'm after is the employee, linked to the training course with the date they completed the course, then maybe another table with the training course and the repeat period? For example:

Auto Id	Employee Name	Employee number
1	Joe Bloggs	111
2	Davey Jones	222
3	Sarah Smith	333

Auto Id	Training Course	Repeat Period
1	Health and Safety Induction	2 years
2	Hygiene / H&S Rules	2 years
3	HSE Charter	2 years

Employee Id	Training Id	Date
1	1	01/10/2018
1	2	05/10/2018
1	3	10/10/2018

Can you help how to make sense of this spreadsheet? Many thanks!!

Kind Regards,

Dayna

[prieper]

not sure, what you are aiming at.

Surely you may load the table with CROSSTABLE-functionality, i.e.

Courses:
    CROSSTABLE(Course, CourseDate, 2) LOAD 
        *
    FROM
    courses.xlsx
    (ooxml, embedded labels, header is 2 lines, table is Data);

This would already give a kind of normalized table with employess and courses (and the date?)

[Dayna]

Hi @Peter,

I've never used the crosstable function, but looks like it would work. How would that work on my spreadsheet? I presume it would still need transforming?

I just tried it with the following:

CrossTable (Name, Date) LOAD

*

FROM

[\\ffle.xls]

(biff, no labels, table is Sheet1$);

And what I get is two fields, but with a mix of data in both. i.e. in 'Date' I get 2 years, the training names, and dates. Whereas in Name, I get a lot of the column references, i.e. @1, @2, @3, ... etc.

Many thanks,
Dayna

[prieper]

Just give it a try ...

In the above example you need to replace

"courses.xlsx"

with the path and name to your excel-file.

It is also possible to read the course-names and link it with the repeating-frequency (from the line above), but think this info a somewhat static. It might be easier to make the transformation within Excel and to load this as further attribute to the courses .

Peter

[Dayna]

Hi Peter,

I have tried but what I get is two fields, but with a mix of data in both. i.e. in 'Date' I get 2 years, the training names, and dates. Whereas in Name, I get a lot of the column references, i.e. @1, @2, @3, ... etc.

Do I need to do any transform before I do this?

Many thanks,

Dayna

[prieper · Accepted solution]

Hi Dayna,

You need to advise QV, which are the fixed dimensions in your CROSSTABLE-extraction,

i.e.

CROSSTABLE(Course, CourseDate, 2) LOAD ...

will take the first two fields as dimensions and then write each following fieldname and -value into a new line (by repeating the dimensions) into fields "Course" and "CourseDate".

HTH Peter