When distinct API used for a column, other columns data of infotable also should present in output

[AP_10343008]

I have 5 columns in infotable. When Distinct API used for first two columns together, the output infotable should have 5 columns with the distinct values of first 2 columns. How to do this

[Velkumar]

Hi @AP_10343008 

You can create query based on your distinct value and merge infotable into single table to get all columns value with distinct value of column1 & column2

// result: INFOTABLE 
let data = me.dummyData();


let params = {
    t: data /* INFOTABLE */ ,
    columns: "Column1,Column2" /* STRING */
};

// result: INFOTABLE
let distinctValue = Resources["InfoTableFunctions"].Distinct(params);

// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE()
let result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
    infoTableName: "InfoTable",
    dataShapeName: "testShape"
});

for (var i = 0; i < distinctValue.length; i++) {
    // Provide your filter using the format as described in the help topic "Query Parameter for Query Services"
    let query = {
        "filters": {
            "type": "And",
            "filters": [{
                    "fieldName": "Column1",
                    "type": "EQ",
                    "value": distinctValue.rows[i].Column1
                },
                {
                    "fieldName": "Column2",
                    "type": "EQ",
                    "value": distinctValue.rows[i].Column2
                }
            ]
        }
    };

    let params = {
        t: data /* INFOTABLE */ ,
        query: query /* QUERY */
    };

    // result: INFOTABLE
    let tempData = Resources["InfoTableFunctions"].Query(params);

    let params2 = {
        t1: result /* INFOTABLE */ ,
        t2: tempData /* INFOTABLE */
    };

    // result: INFOTABLE
    result = Resources["InfoTableFunctions"].Union(params2);

}

/VR

[AP_10343008]

As I have fetched 4000 data from windchill, it will take more time to use for loop. So any ither time saving way is there to get all column data by making 2 columns distinct.

[Velkumar · Accepted solution]

Hi @AP_10343008 

It won't take much time. Because you already pulled 4000 rows of data from Windchill and filtered distinct values out of it. 

The script will reuse the initially pulled data from the Windchill System it won't make any new calls to Windchill system

/VR

[AP_10343008]

I passed all the 5 columns to distinct Infotable API, this gives output faster than forloop.

[Velkumar]

Hmmm... How much does the loop take to complete ?

One more way you can build a query based on distinct values and use Query snippet to filter infoTable.

// result: INFOTABLE 
let data = me.dummyData();

let params = {
    t: data /* INFOTABLE */ ,
    columns: "Column1,Column2" /* STRING */
};

// result: INFOTABLE
let distinctValue = Resources["InfoTableFunctions"].Distinct(params);

let query = {
    "filters": {
        "type": "OR",
        "filters": []
    }
};

for (var i = 0; i < distinctValue.length; i++) {
    // Provide your filter using the format as described in the help topic "Query Parameter for Query Services"
    let tempQuery = {
        "type": "AND",
        "filters": [{
                "fieldName": "Column1",
                "type": "LIKE",
                "value": distinctValue.rows[i].Column1
            },
            {
                "fieldName": "Column2",
                "type": "LIKE",
                "value": distinctValue.rows[i].Column2
            }
        ]
    };
    query.filters.filters.push(tempQuery);

}

let params2 = {
    t: data /* INFOTABLE */ ,
    query: query /* QUERY */
};

// result: INFOTABLE
let result = Resources["InfoTableFunctions"].Query(params2);

Also, could you please explain your use case a bit more? 

Based on my dummy data, adding other columns returns me the actual data. So it will be good if you example with some sample data

/VR

[AP_10343008]

Thanks for your help. As I have 4000 rows after distinct, I want to avoid for loop with these data. As the remaining 3 column having unique values by default, I provided all 5 columns in distinct API. Now it is working as expected.