Matrix formated table by region and services

App Building
1

I am trying to make the matrix-like table by fixed columns and rows using mysql

As you see below table, the row data is regions seq (only 20) and column data is service type seq and the yellow area should be value that matchs by column and row.

How can I make it?

image

For you understanding, I add the screenshot that I did at the moment

image
image2146×1020 161 KB

2

So what you want it to take an array like this:

const array1 = [
  { regionSeq: 1001, serviceTypeSeq: 210, dataCount: 3 },
  { regionSeq: 1001, serviceTypeSeq: 220, dataCount: 7 },
  { regionSeq: 1002, serviceTypeSeq: 220, dataCount: 5 },
  { regionSeq: 1003, serviceTypeSeq: 330, dataCount: 36 },
  { regionSeq: 1004, serviceTypeSeq: 310, dataCount: 11 }
]

and transform it into an array like this:

const array2 = [
  { '210': 3, '220': 7, '310': null, '330': null, regionSeq: 1001 },
  { '210': null, '220': 5, '310': null, '330': null, regionSeq: 1002 },
  { '210': null, '220': null, '310': null, '330': 36, regionSeq: 1003 },
  { '210': null, '220': null, '310': 11, '330': null, regionSeq: 1004 }
];

Am I getting this right?

3

Yes, correct.
In addition, I would like to make the table which looks like a matrix of fixed field of rows (regional Seq) and columns (service type seq).

Just to make sure, the regionSeq is not the value but the dimension which is like the service type seq.
thanks,

4

To build a matrix-like data from initial database request you may create JS query like this:

const uniqueServiceTypeSeqArr = [ ...new Set(array1.map((item) => item.serviceTypeSeq))];
const uniqueRegionSeqArr = [...new Set(array1.map((item) => item.regionSeq))];

const transformedData = uniqueRegionSeqArr.map((regionSeqItem) => {
  const newObj = { regionSeq: regionSeqItem };

  uniqueServiceTypeSeqArr.forEach((serviceTypeSeqItem) => {
    const arr1Item = array1.find(
      (el) =>
        el.regionSeq == regionSeqItem && el.serviceTypeSeq == serviceTypeSeqItem
    );

    newObj[serviceTypeSeqItem] = arr1Item ? arr1Item.dataCount : null;
  });
  return newObj;
});

return transformedData

Here's screenshot demonstrating this:

CleanShot 2024-01-14 at 4 .09.05@2x
CleanShot 2024-01-14 at 4 .09.05@2x2974×1062 375 KB

5

Hey @tax - I wonder if the above script worked for you.

It can can be overwhelming to apply someone else’s code so feel free to share some details if you having difficulties implementing it

6

It works for me!!!
Thanks, Preshetin

7

@tax happy it worked. I suggest you mark it as a Solution then

8

I did it as you mentioned. I am sorry not to mark it as a solution as I am a new user of retool therefore I am not used to forum as well.
Thanks,

1 Like
9

I think you are doing great @tax. It’s been a joy for me looking into your topics

1 Like