How can we help?

New post
1

API Reporting with When I Work

Avatar
sohail sarwar
None
Follow

I have seven columns, and am polling the API for When I Work (WIW).  I have the following six columns:

 

FIRST NAME             LAST NAME         FULL NAME              MANAGER                  WIW ID                  PTO FROM            PTO TO
   ADAM                         MADA                  ADAM MADA             PAUL SIMON               34411914              11/1/18 07:00         11/7/18 07:00
   AMY                            MADA                  AMY YMA                  NOMIS LUAP               34074695              12/24/18 08:00      12/29/18 08:00
   ROGER                     REGOR                ROGER REGOR        PETER PARKER         21685259              12/20/18 08:00     12/24/18 08:00
   AMY                            MADA                  AMY YMA                  NOMIS LUAP               34074695              12/05/18 08:00      12/12/18 08:00

FIRST NAME = GROUP(slice(cd0e253394085e367c00a5a79f967768@/users/first_name;))
LAST NAME = (LOOKUP(!'dc0c578f-2',cd0e253394085e367c00a5a79f967768@/users/first_name;, cd0e253394085e367c00a5a79f967768@/users/last_name;))
FULL NAME = CONCAT(!'dc0c578f-2', " ", !'25e1bc36-5')
MANAGER = LOOKUP(!'e9d6b607-8',d20b7c1ff4c9dfb1a90db48efc02aba6@A:A;, d20b7c1ff4c9dfb1a90db48efc02aba6@H:H;)
       NOTE @A:A is from "Agent Report" that have the Full Name of the Engineer
                  @H:H is from "Agent Report" that has the User Manager name
WIW ID = LOOKUP(&'dc0c578f-2',cd0e253394085e367c00a5a79f967768@/users/first_name;, cd0e253394085e367c00a5a79f967768@/users/id;)
PTO FROM = LOOKUP(!'25e1bc36-5',43034dd80aec44c610b1a490a28b6ffa@/users/last_name;, 43034dd80aec44c610b1a490a28b6ffa@/requests/start_time;)
PTO TO = LOOKUP(&'dc0c578f-2',cd0e253394085e367c00a5a79f967768@/users/first_name;, ARRAY(43034dd80aec44c610b1a490a28b6ffa@/requests/end_time;))

The issue is that I want to display all the records based on the ID for PTO, from and to.. and I am not able to get it to work.  This is basically to track the engineers time off, so the Columns of PTO have to compliment each other for reference and should have all the enteries that match the WIW ID.

 

 

 

Date Votes

1 comment

  • 0
    Avatar
    Janice Janczyn

    Hi Sohail,

    I'm not sure what your actual datasource looks like, but I suspect this could be simplified if the data is consistent, that is, if every record in your datasource contains all the same fields. In that case, all you need to do is point each column in your table klip to a field rather than using a LOOKUP. Also I suspect, since this is JSON data, there is no header row, so no need to use SLICE. You may have to email support@klipfolio.com with further details so we can see your data, but as a starting point, please try:

         FIRST NAME = cd0e253394085e367c00a5a79f967768@/users/first_name;
         LAST NAME = cd0e253394085e367c00a5a79f967768@/users/last_name;
         FULL NAME = cd0e253394085e367c00a5a79f967768@/users/full_name; (if available) or CONCAT(&FIRST NAME, " ", &LAST NAME)

         MANAGER = LOOKUP(&FULLNAME, d20b7c1ff4c9dfb1a90db48efc02aba6@A:A;, d20b7c1ff4c9dfb1a90db48efc02aba6@H:H;)
         WIW ID = cd0e253394085e367c00a5a79f967768@/users/id;

    Then use the Actions menu to group FULL NAME and set Aggregation to First for the FIRST NAME, LAST NAME and WW ID columns. When 1 column in a table is grouped, all other columns are automatically aggregated, but you have to specify how you want the other columns aggregated.

    I hope this helps. If you need further assistance, please email support@klipfolio.com.

     

    Thank you,
                Janice

Please sign in to leave a comment.