Creates a Pivot View for Quotations, will be used in Retool

pivotView.js

// Tip: assign your external references to variables instead of chaining off the curly brackets.

let list = quotationsList.data

var flattenedList = []

for (let index = 0; index < list.Id.length; index++) {
    flattenedList.push({
        Id: list.Id[index],
        MPN: list.MPN[index],
        DistributorName: list.DistributorName[index],
        DistributorContactName: list.DistributorContactName[index],
        Date: list.Date[index].substring(0,10),
        MOQ: list.MOQ[index],
        QuotedQuantity: list.QuotedQuantity[index],
        Currency: list.Currency[index],
        UnitPrice: list.UnitPrice[index],
        LeadTime: list.LeadTime[index],
        PackageType: list.PackageType[index],
        Note: list.Note[index]
    }
    )
    
}


var distAndDates = []
const splitter = "<-->"

flattenedList.forEach(item => {
    let combination = {"dist":item.DistributorName, "date": item.Date} //item.dist + " - " + item.date
    distAndDates = [...new Set(flattenedList.map(item => item.DistributorName + splitter + item.Date))];
});


var newList = []

let uniqueMPNs = [...new Set(flattenedList.map(item => item.MPN))];
console.log("uniqueMPNs")
console.log(uniqueMPNs)

uniqueMPNs.forEach(material => {
    var newItem = {MPN: material}
    distAndDates.forEach(diDT => {
        let diANDdt = diDT.split(splitter)
        let dist = diANDdt[0]
        let date = diANDdt[1]
        let matchingPrices = flattenedList.filter(item => item.MPN === material && item.Date === date && item.DistributorName === dist)
        if(matchingPrices.length > 0 ){
            newItem[dist+splitter+date] = matchingPrices[0].UnitPrice
        } else{
            newItem[dist+splitter+date] = 0

        }
    });
    newList.push(newItem)
});

console.log("newList")
console.log(newList)


var seperatedList = {
        MPN:[]
    }

Object.keys(newList[0]).slice(1).sort().forEach(key => {
    seperatedList[key] = []
    });

for (let index = 0; index < newList.length; index++) {
       seperatedList.MPN[index] = newList[index].MPN
       // Get all the distributor price names
       Object.keys(newList[0]).slice(1).sort().forEach(key => {
        seperatedList[key][index] = newList[index][key]
       });
    }

console.log("seperatedList")
console.log(seperatedList)

let data = seperatedList

return data