Merge different sources, and hide unnecessary info.

[robin_heijt]

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.

[Anonymous]

I got you! wait a minute, i am working on it!

[Anonymous]

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

[robin_heijt]

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!

[Anonymous]

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

[Quy_Nguyen · Accepted solution]

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;

[Anonymous]

create a final table with all worked up, nice.