Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I currently have 3 data sources,
1: General script
2: OPR 2017
3: OPR 2018
What syntax would I have to use to combine the 3 of these into one row within the Data Load Editor while hiding the name from the OPR files.
Essentially creating a list with people from the general file, and if matching add OPR score as a dimension linked by Global ID present in all 3 files.
This is part of my first data source:
// General Load Script
LOAD
"Global ID",
"Personnel Number",
Capitalize("First name"&' '& "Last name") as "Full Name",
"Employment Status Text" as "Employment Status",
"Employee group",
"Name of employee grp" as "Employee Group"
FROM [lib://AttachedFiles/1Europe.xlsx]
(ooxml, embedded labels, table is Headcount);
// OPR 2017
load *, keepchar("Last Published OPR Rating 2017",'4A4B3A3B21A1B') as "OPR 2017";
LOAD
"Employee Global ID" as "Global ID",
Capitalize(Name) as "Full Name",
// "Position Title of Record",
// "Summary Competency Rating - Numeric Value",
// "Summary Competency Rating - Scale Value",
// "Preliminary OPR Rating",
"Last Published OPR Rating" as "Last Published OPR Rating 2017"
// "Year",
// "Plan Name",
// "Review Period Start Date",
// "Review Period End Date",
// "Final Evaluation Workflow State"
FROM [lib://AttachedFiles/OPR 2017.xlsx]
(ooxml, embedded labels, table is [OPR 2017]);
General names:
as a sample list (5 names out of 15.000)
Global ID - Full Name - etc..
1 - Harry
2 - Jack
3 - Dina
4 - Maria
5 - Sanne
19 - Jord
OPR 2017 names:
Global ID - Full name - OPR 2017 - etc...
1 - Harry - 4
2 - Jack - 3
3 - Dina - 4
4 - Maria - 5
14 - Dirk - 3
17 - Jenny - 2
19 - Jord - Blank
So I would want the list to show only the names from the general script:
1 - Harry - 4
2 - Jack - 3
3 - Dina - 4
4 - Maria - 5
5 - Sanne - "No Score" (If no result in OPR 2017 fill "No Score")
19 - Jord - "No Score" (If blank OPR Score fill "No Score")
Thank you.
If you just want to get data of your general script, just do:
// General Load Script
Temp:
LOAD
"Global ID",
"Personnel Number",
Capitalize("First name"&' '& "Last name") as "Full Name",
"Employment Status Text" as "Employment Status",
"Employee group",
"Name of employee grp" as "Employee Group"
FROM [lib://AttachedFiles/1Europe.xlsx]
(ooxml, embedded labels, table is Headcount);
Left Join(Temp)
// OPR 2017
LOAD
"Employee Global ID" as "Global ID",
Keepchar("Last Published OPR Rating 2017",'4A4B3A3B21A1B') as "OPR 2017"
FROM [lib://AttachedFiles/OPR 2017.xlsx]
(ooxml, embedded labels, table is [OPR 2017]);
NoConcatenate
FinalData:
Load
"Global ID",
"Personnel Number",
"Full Name",
"Employment Status",
"Employee group",
"Employee Group",
If(isNull("OPR 2017") or "OPR 2017" = '', 'No Score',"OPR 2017") As "OPR 2017"
Resident Temp;
Drop Table Temp;
I got you! wait a minute, i am working on it!
This is what you should do:
// General Load Script
LOAD
"Global ID",
"Global ID" as IDreference, // add this
"Personnel Number",
Capitalize("First name"&' '& "Last name") as "Full Name",
"Employment Status Text" as "Employment Status",
"Employee group",
"Name of employee grp" as "Employee Group"
FROM [lib://AttachedFiles/1Europe.xlsx]
(ooxml, embedded labels, table is Headcount);
This small thing can make then you filter your IDs from this general list.
in set análisis, do this: //those examples count the number of IDs in the tables that matches the ones inside General Load.
in KPI:
= count ( {<IDreference="<>null()">} [Global ID] )
//will filter to only people with the id inside General Load
in expression:
count ( if ( IDreference <> null() , [Global ID] , 0 ) )
Like, Mark as helpful, Mark as the answer if it is. It helps a lot, you can do all three!
Thanks
Thank you!
The first part is pretty clear, however I don't understand the part with set analysis.
I am sorry, but I just started using the data load editor this week, so still pretty new, but willing to learn!
No problem!
Starting with Filters:
Filters are something that you use to get only a part of the data. The native filters will filter ALL DATA from your application. So, if you want something to work that doesn't change everything, you can create a filter that can be only on 1 formula. That is:
if I do this on a KPI:
=count ( [Global ID] )
it will give me the number of people in the database that has a Global ID. Probably everyone.
but if i do this:
=count ( {<[OPR 2017]={'4'}>} [Global ID] )
this will give me the number of people in the database that has scored 4 points!
but to see your solution it could be:
=if(IDreference<>null(),
if ( OPR 2017 = null(),
'No Score', OPR 2017) )
this will go inside a KPI or table. in this case, would work as value on a table.
it goes like this:
if ( IDreference <> null // that means this person exists inside General ID
then true goes:
if ( OPR 2017 = null // that means this person doesn't have a score
then true goes:
'No Score' // the value would be 'No Score' if OPR 2017 is void
then false goes:
OPR 2017) ) // the value would be the actual score because it is not void
is that clear?
Like, Mark as helpful, Mark as the answer if it is. It helps a lot, you can do all three!
Thanks
If you just want to get data of your general script, just do:
// General Load Script
Temp:
LOAD
"Global ID",
"Personnel Number",
Capitalize("First name"&' '& "Last name") as "Full Name",
"Employment Status Text" as "Employment Status",
"Employee group",
"Name of employee grp" as "Employee Group"
FROM [lib://AttachedFiles/1Europe.xlsx]
(ooxml, embedded labels, table is Headcount);
Left Join(Temp)
// OPR 2017
LOAD
"Employee Global ID" as "Global ID",
Keepchar("Last Published OPR Rating 2017",'4A4B3A3B21A1B') as "OPR 2017"
FROM [lib://AttachedFiles/OPR 2017.xlsx]
(ooxml, embedded labels, table is [OPR 2017]);
NoConcatenate
FinalData:
Load
"Global ID",
"Personnel Number",
"Full Name",
"Employment Status",
"Employee group",
"Employee Group",
If(isNull("OPR 2017") or "OPR 2017" = '', 'No Score',"OPR 2017") As "OPR 2017"
Resident Temp;
Drop Table Temp;
create a final table with all worked up, nice.