6 Replies Latest reply: Aug 23, 2018 12:44 PM by Felipe Andrade RSS

    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.

        • Re: Merge different sources, and hide unnecessary info.
          Felipe Andrade

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

            • Re: Merge different sources, and hide unnecessary info.
              Felipe Andrade

              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

                  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.
                      Felipe Andrade

                      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

                  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;