Data manipulation - Pivot - unpivot ? loop ? for? I need summarize data

[fiorelafantasia]

Hello everyone, can anyone help me?

I have the following data:

Course ID Satus
A peralta Completed
A peralta Cancelled
C peralta Completed
C peralta Enrolled
B peralta No Show
A gomez Cancelled
A gomez Completed
C gomez Cancelled
C gomez Cancelled
A tarantino Cancelled
A tarantino Enrolled
C tarantino Cancelled
C tarantino Enrolled
C tarantino Completed

and i would like to have something like this:

Course ID Satus
A peralta Completed
C peralta Completed
B peralta Not Completed
A gomez Completed
C gomez Not Completed
A tarantino Not Completed
C tarantino Completed

The "Complete" status is priority and replaces every possible status, if the person does not complete the course, then the status must be "Not Complete". We must consider ID and Course. 

I think that i need something like a Loop. or group by, I've tried but it didn't work.

Thanks to all !

[JordyWegman · Accepted solution]

Hi Fiorela,

This is a solution:

MapStatus2Number:
Mapping Load * Inline [
StatusNr, Status
4, Completed
3, Cancelled
2, Enrolled
1, No Show
];

MapNumber2Status:
Mapping Load * Inline [
Status, Nr
Completed, 4
Cancelled, 3
Enrolled, 2
No Show, 1
];

Table:
Load
Course,
ID,
ApplyMap('MapStatus2Number',StatusNr,0) as Status
;
Load
Course,
ID,
Max(ApplyMap('MapNumber2Status',Status,0)) as StatusNr
Group by Course,ID
;
Load * Inline [
Course, ID, Status
A, peralta, Completed
A, peralta, Cancelled
C, peralta, Completed
C, peralta, Enrolled
B, peralta, No Show
A, gomez, Cancelled
A, gomez, Completed
C, gomez, Cancelled
C, gomez, Cancelled
A, tarantino, Cancelled
A, tarantino, Enrolled
C, tarantino, Cancelled
C, tarantino, Enrolled
C, tarantino, Completed
];

Jordy

Climber

[fiorelafantasia]

Appreciate your help, i will try to migrate this solution to my workspace.

Thanks you!!

[fiorelafantasia]

tabla1:
LOAD
[Course Name],
[Enterprise ID],
if([Activity Status]='Completed','2',if([Activity Status]='Waived','2','1')) as StatusNro,
if([Activity Status]='Completed','2',if([Activity Status]='Waived','2','1')) as StatusNro1,
if([Activity Status]='Completed','Completed',if([Activity Status]='Waived','Completed','Not Completed')) as Status2
FROM [lib://Analytics Learning/Learning Dashboard.xlsx]
(ooxml, embedded labels, table is Learning)
;

MapStatus2Number:
Mapping Load
StatusNro,
Status2
Resident tabla1
;

MapNumber2Status:
Mapping Load
Status2,
StatusNro1
Resident tabla1
;

Table12345:
Load
[Enterprise ID],
[Course Name],
ApplyMap('MapStatus2Number',StatusNro,0) as Status2
;

Load
[Enterprise ID],
[Course Name],
Max(ApplyMap('MapNumber2Status',Status2,0)) as StatusNro
Group by [Course Name],[Enterprise ID]
;

Any suggestion? i have the following issue in Data Manager when i try to refresh:

Data load failed. Resolve the problem and refresh the data. "Field Course Name not found"

Thanks in advance!

[Channa]

You can try

If([Activity Status]='Completed' or[Activity Status]='Waived','2','1'))

[JordyWegman · Accepted solution]

Hi Fiorela,

You are missing a resident load in your last table:

tabla1:
LOAD
[Course Name],
[Enterprise ID],
if([Activity Status]='Completed','2',if([Activity Status]='Waived','2','1')) as StatusNro,
if([Activity Status]='Completed','2',if([Activity Status]='Waived','2','1')) as StatusNro1,
if([Activity Status]='Completed','Completed',if([Activity Status]='Waived','Completed','Not Completed')) as Status2
FROM [lib://Analytics Learning/Learning Dashboard.xlsx]
(ooxml, embedded labels, table is Learning)
;

MapStatus2Number:
Mapping Load
StatusNro,
Status2
Resident tabla1
;

MapNumber2Status:
Mapping Load
Status2,
StatusNro1
Resident tabla1
;


Table12345:
Load
[Enterprise ID],
[Course Name],
ApplyMap('MapStatus2Number',StatusNro,0) as Status2
;
Load
[Enterprise ID],
[Course Name],
Max(ApplyMap('MapNumber2Status',Status2,0)) as StatusNro
Resident tabla1
Group by [Course Name],[Enterprise ID]
;

 Can you try this again?

Jordy

Climber

[fiorelafantasia]

it worked. Thank you !! I appreciate all your help

[JordyWegman · Accepted solution]

Great! Please mark the post as solved!

Jordy

Climber