topic Re: Merge different sources, and hide unnecessary info. in App Development

Merge different sources, and hide unnecessary info.

robin_heijt — Thu, 23 Aug 2018 15:29:56 GMT

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.

Re: Merge different sources, and hide unnecessary info.

Anonymous — Thu, 23 Aug 2018 15:39:39 GMT

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

Re: Merge different sources, and hide unnecessary info.

Anonymous — Thu, 23 Aug 2018 15:50:34 GMT

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

Re: Merge different sources, and hide unnecessary info.

robin_heijt — Thu, 23 Aug 2018 16:01:14 GMT

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!

Re: Merge different sources, and hide unnecessary info.

Anonymous — Thu, 23 Aug 2018 16:22:08 GMT

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

Re: Merge different sources, and hide unnecessary info.

Quy_Nguyen — Thu, 23 Aug 2018 16:35:54 GMT

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;

Re: Merge different sources, and hide unnecessary info.

Anonymous — Thu, 23 Aug 2018 16:44:24 GMT

create a final table with all worked up, nice.