Help

Data mining - Trying to create a sheet with keywords (rows) that show how many times they appear and where

Topic Labels: Formulas
4485 19
cancel
Showing results for 
Search instead for 
Did you mean: 
Travis_Johnson1
6 - Interface Innovator
6 - Interface Innovator

I have a decently large data set, and I am trying to mine from it information to see what has value. On that I am trying to create a new sheet that has a list of keywords. I would like to then have a column next to each keyword that displays how many times that word appears in my main sheet (or a single column).

I have seen several topics that cover something similar but nothing that seems to fit the bill.

Can anyone help? Thanks!

19 Replies 19

Not so fast - I haven’t actually solved this riddle yet. :winking_face:

Is this sort’a what you’re looking for?

image

Travis_Johnson1
6 - Interface Innovator
6 - Interface Innovator

Sorry about the delay, the notifier email went to my spam folder. That is exactly what I am looking for!!!

Okay - good to hear!

Share with me (in a direct message) your Airtable account email address and I’ll share the Base to you to examine the analytics. Once you confirm this is what your goal is, I’ll drop the script block to you, install it into the base and add more content and keywords to further test.

image

Travis_Johnson1
6 - Interface Innovator
6 - Interface Innovator

That’s better then what I was looking for!

Ru_Leamon
4 - Data Explorer
4 - Data Explorer

Hey,

This is amazing and exaclty the stort of thing that I’m trying to do. Is there any way that you would be able to share it with me please?

Thanks

Michael_Tranchi
4 - Data Explorer
4 - Data Explorer

This is also the exact solution I am looking for…Any chance of getting a copy of the script block?

Kay_Aleksic
4 - Data Explorer
4 - Data Explorer

I’d like to know too if this is available and where.

@Kay_Aleksic, @Michael_Tranchina - here’s the script.

/*

   ***********************************************************
   ScriptBloqs - Datamining Text
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

//
// get the table names and records
//
output.markdown('# Data Mining');
output.markdown('## Updating Metrics...');

// Load all of the terms records in the keywords and phrases table
let termsTable      = base.getTable("Keywords & Phrases");
let queryResults    = await termsTable.selectRecordsAsync();
let oTermsRecords   = queryResults.records;

// Load all of the content records in the table
let contentTable    = base.getTable("Content");
queryResults        = await contentTable.selectRecordsAsync();
let oContentRecords = queryResults.records;

// process the terms list
let oSourceGrid  = {};
let oResultsGrid = {};
let i = 0;
for (let record of oTermsRecords)
{

    var thisTerm = record.getCellValue("Key Terms").toString().toLowerCase();
    // output.inspect(thisTerm);

    let summaryCount   = 0;
    let contentCount   = 0;
    let inBothCount    = 0;
    let aSerialNumbers = [];

    for (let record of oContentRecords)
    {

        var thisSerialNumber = record.getCellValue("Serial #");
        var thisSummary      = record.getCellValue("Summary");
        var thisContent      = record.getCellValue("Content");

        var inSummary = (thisSummary.toString().toLowerCase().indexOf(thisTerm) > -1) ? true : false;
        var inContent = (thisContent.toString().toLowerCase().indexOf(thisTerm) > -1) ? true : false;

        if (inSummary)
            summaryCount ++;

        if (inContent)
            contentCount ++;

        if ((inSummary) && (inContent))
            inBothCount ++;

        if ((inSummary) || (inContent))
          aSerialNumbers.push(thisSerialNumber);

        oSourceGrid[thisSerialNumber] = {
            "Summary"       : thisSummary,
            "Content"       : thisContent
        }


    }

    oResultsGrid[i] = {
        "Key Term"       : thisTerm,
        "In Both Count"  : inBothCount,
        "Summary Count"  : summaryCount,
        "Content Count"  : contentCount,
        "Serial Numbers" : aSerialNumbers.toString()
    }

    output.text("Creating entry for " + thisTerm + "!");
    await termsTable.updateRecordAsync(record.id, {
        "In Both Count"  : inBothCount,
        "Summary Count"  : summaryCount,
        "Content Count"  : contentCount,
        "Serial Numbers" : aSerialNumbers.toString()
    })

    i ++;

}

output.clear();
output.markdown('# Data Mining');
output.markdown('## Metrics Updated');

output.markdown('### Content');
output.table(oSourceGrid);

output.markdown('### Metrics');
output.table(oResultsGrid);

This is so great. Would you mind sharing the link to your base with me?

Glad you like it.

Yes, actually I would because the code example was based on a client’s data. And I wish I had lots of idle time to create another data example, but I’m swamped with client projects that will lead to sharing other script examples. :winking_face: