and so on with a lot more possible values for roles.
During the load script we did a if(match) like in the script below to get the data into more columns based on a match on the Role field . so we should now have:
However when we list the rows in a table field for e.g. to export back to excel we see we have the below results where the contacts are split into different rows for each Name-sysid combination:
How do I get the table to look like how we expect it to be, in one row with all the relevant fields based on the Name and sys_id.
My load script looks as below:
LOAD DISTINCT
Name,
sys_id,
if(Match([role],'1st contact'),[Name]) as [1st contact Name],
if(Match([role],'1st contact'),Email) as [1st contact Email],
if(Match([role],'2nd contact'),[Name]) as [2nd contact Name],
if(Match([role],'2nd contact'),Email) as [2nd contact Email],
if(Match([role],'E-owner'),[Name]) as [E-owner Name],
if(Match([role],'E-owner;'),Email) as [E-owner Email]
FROM
(ooxml, embedded labels, table is Page1_1);
Please advice. I tried doing a left join on self and a noconcatenate for a final table, but I haven't been able to get this right. Thanks for your help in advance.
The solution you provided worked on the sample data, however when I try to apply it to my main spreadsheet with about 60K rows, the straight table is not getting populated. Not sure whats causing the issue. I am able to see the data in a table box ,without the only(if) conditions of course, but it doesn't seem to like something in the chart. Below is what I have for the expression definition
for e.g. only(if(Role = 'First Call Support Contact', [full name]))
