How can we help?

New post
1

Combining JSON datasources

Avatar
Catherine Parkinson
None
Follow

Hi,

I've got a bit crazy reading through all of the help files, I'm sure I'm over complicating things now.

I have an api to a service called Knack that returns 2500+ records (this will grow), due to the api restricitions I can only return 1,000 records at a time so I have set up three data sources containin all the records DS1(0-1000), DS2(1001,2000) etc... Now I'm trying to find how to use of these records in klips and getting confused ...

An example. All the fields are just called field_1, field_2 and I want to find out how many records there are for each employee (field_29 is employee initial)

DS1 : GROUPBY(@/records/field_29;,@/records/field_29;,"count(values)")

DS2 : GROUPBY(@/records/field_29;,@/records/field_29;,"count(values)")

Both of these formula give me a count for each datasource. How can I combine into one result ?

I have tried : GROUPBY(array@/records/field_29;,@/records/field_29;),array(@/records/field_29;,@/records/field_29;),"count(values)") where the first field_29 is DS1 and the second is DS2 but it gives a weird result :

I would like the COUNT JOIN column to show the results as the addition of the other two and I don't know why it isn't working. There are no gaps in data and no missing fields (it's mandatory)

 

Apologies if this is very basic... I'm getting tied up in knots !

 

Date Votes

7 comments

  • Avatar
    Josh Cohen-Collier Official comment

    Hi Catherine,

    Thanks for using Klipfolio's Community.

    I believe the function are looking for here is the COUNTDISTINCT function.
    In your first column, the formula would be using both ARRAY, and GROUP.
    GROUP(ARRAY(@/records/field_29;,@/records/field_29;)

    Your second column would be the following;
    COUNTDISTINCT(ARRAY(@/records/field_29;,@/records/field_29;)

    Hope this helps!

    If you are still seeing issues in your table, it might be caused by the fact that the data is not aligned to your first column.
    For instance, if the first column only has the initials from the first datasource, then the results for the DS2 and Joined column may have entries that are not included, and thus the two table columns would not line up.

    If you are to have these columns which draw from only one DS, in the same table as a column with both DS, you will need to use the LOOKUP function.
    Eg;
    LOOKUP(
        GROUP(ARRAY(@/records/field_29;,@/records/field_29;),//All initials from both DS
        GROUP(@/records/field_29;),//All initials from one DS
        COUNTDISTINCT(@/records/field_29;)//Count of initials from the one DS
    )

    Best regards,
    Joshua

  • Avatar
    Janice Janczyn Official comment

    Hi Catherine,

    This is definitely a bit advanced. Many web services, such as Knack, return data in "pages" instead of all the data in 1 response. Knack responses return a value for total_records  which can be used, along with the rows_per_page parameter, to calculate how many pages you need to request.. Without the page parameter, the following query will by default return the first page of data, containing a maximum of 1000 rows of data:

        https://api.knack.com/v1/objects/<objectID>/records?rows_per_page=1000

    To get the subsequent pages using the variable, page:

        https://api.knack.com/v1/objects/<objectID>/records?rows_per_page=1000&page={props.page}

    For details, see Datasources with Multiple Pages.  Using this approach, you won't have to manually create a new datasource as your Knack object grows.

    Instead of GROUPBY( @/records/field_29, @/records/field_29, "count(values)" ), use COUNTDISTINCT( @/records/field_29 ). Using your current approach with multiple datasources, you would use

       COUNTDISTINCT( ARRAY( @/records/field_29,@/records/field_29 ) )

    to create an array of field_29 from the different datasources, and then counting the number in each distinct group. I suspect the issue you`re seeing is because the data in your columns is not aligned. Use the LOOKUP function to align the data:

       INITIALS: GROUP( ARRAY( @/records/field_29,@/records/field_29, @/records/field_29 ) )

       COUNT DS1: LOOOKUP( !INITIALS, GROUP(@/records/field_29), COUNTDISTINCT(@/records/field_29) )   '<--- these fields point to DS1

       COUNT DS2: LOOOKUP( !INITIALS, GROUP(@/records/field_29), COUNTDISTINCT(@/records/field_29) )   '<--- these fields point to DS2

       COUNT JOIN: COUNTDISTINCT( ARRAY( @/records/field_29,@/records/field_29, @/records/field_29 ) )

    The INITIALS and COUNT JOIN columns are naturally aligned because the GROUP, GROUPBY and COUNTDISTINCT functions are designed to align the data when they are passed the same parameters.

    Thanks,

           Janice

  • 0
    Avatar
    Catherine Parkinson

    Thank you so much for your responses. I'm relieved that this isn't a simple query that I'm getting drastically wrong :)

    I have managed to use your suggested formula and have got the table working as required, but if there is away I can get the incoming data to form one datasource instead of having to stitch it all together then it makes life a ton easier !

    I admit I have looked at both :

    https://support.klipfolio.com/hc/en-us/articles/218946328-Data-Sources-with-Multiple-Pages-

    https://support.klipfolio.com/hc/en-us/articles/216183237-BETA-Working-with-dynamic-data-sources

    And am stumped by how to get the value of the total pages into a variable to then use in the api url - the {props.page} part of your example above. Do you have any advice that can clarify this for me ?

    Thanks for your time and patience !!

  • 0
    Avatar
    Janice Janczyn

    Hi Catherine,

    With paginated data, you cannot get the data from 1 datasource. You need 1 static datasource to find out the total number of records in the response and then you can use that information to calculate the number of pages needed and set the page parameter in a dynamic version of the static datasource.

    Use the MAPFLAT function (instead of the MAP function as described in the Datasources with Multiple Pages article) to set the page variable. You'll have to create the "page" variable first; see Variables.

       MAPFLAT( CUMULATIVE(REPEAT( 1, total_pages),
                          "page",
                          @/records/field_29 )

    Thanks,
            Janice

  • 0
    Avatar
    Catherine Parkinson

    Hi,

    Thank you for taking the time to explain this to me, however I still don't understand this :( 

    1) Dynamic Variable

    To create the "page" variable you state to create a variable with the MAPFLAT function, however this only seems to save the text of the MAPFLAT function not the value. I have created a new Klip and gone to Advanced Tools > Create New Variable > Given a Variable Name > then in Klip Editor Value .... do I just paste the MAP function text ? I can't find anywhere what to actually type here to enable me to use $page. I'm definitely missing something I would rather not type the value 3 as it undermines the point of being variable.

    2) When using the MAPFLAT function in the a table klip I have manually added a number instead of the variable in your example and each time the output is the same, it returns 3,000 if I use another column with the formula : count(!Column:Data) where as there are only 2610 total records.

                         MAPFLAT( CUMULATIVE(REPEAT( 1, total_pages),
                          "2",
                          @/records/field_29 )

     

    Would I have to use the MAPFLAT function stead of the second array in every other formula I write to get other data ? So instead of writing :

    count(array(@/records/field_8_raw;,@/records/field_8_raw;,@/records/field_8_raw;)) (Where I have DS1,DS2,DS3) which gives the answer of 2610

    Would it have to be 

    count(array(@/records/field_8_raw;,MAPFLAT( CUMULATIVE(REPEAT( 1, @/total_pages;),"2",@/records/field_8_raw; )))) Which gives the answer of 1,000 no matter what I type instead of "2"

    What am I just not getting ?

    Thank you again for your patience !

     

  • 0
    Avatar
    Catherine Parkinson

    UPDATE :

    I've still not worked out the Dynamic Variable stuff so for now I am manually typing in a figure (1,2 or 3) for testing purposes under the variable $page as suggested.

    I have been able to construct a dynamic api with the example and return data ! However when using in a Klip I've run into issues. I have created the Klip with 2 datasources STATIC and DYNAMIC (using the variable $path with the value 3)

    I know I have 2610 records

    When using count(array(@/records/field_8;,@/records/field_8;)) I get the answer 1,610 and it seems to be counting ONLY page 1 (static) and page 3

    When using count(array(@/records/field_8;,MAPFLAT( CUMULATIVE(REPEAT( 1,@/total_pages;),$page,@/records/id;))))  I get the answer 1,000 

    Here's a link to a text file of the klip source if that helps to demonstrate what I'm doing ... http://energiseconsulting.com/cloudstore/index.html?/share/view/gwhd-8e3wqkgg 

  • 0
    Avatar
    Janice Janczyn

    Hi Catherine,

    The 2nd parameter of the MAPFLAT must be entered as a text string (also it appears your parenthesis are mismatched, I've fixed them here): 

       COUNT( MAPFLAT( CUMULATIVE(REPEAT( 1,@/total_pages) ),
                                         "page",
                                        @/records/field_8 ) )

    This will count the number of /records/field_8 items. In the above, /total/pages references your static datasource

         https://api.knack.com/v1/objects/<objectID>/records?rows_per_page=1000

    and /records/field_8 references your dynamic datasource, which uses the page variable, prefixed with props. and wrapped in {}.

         https://api.knack.com/v1/objects/<objectID>/records?rows_per_page=1000&page={props.page}

    It isn't necessary to wrap ARRAY around the MAPFLAT. 

    If you'd like, you can email support@klipfolio.com and enable support access so we have access to your account.

     

    Thank you, 
                 Janice

Please sign in to leave a comment.