Data: LOAD * Inline [ Company,StatementLine,TransactionAmount A,'Sales',150 A,'Cost of Sales',100 ]; Concatenate load Company, 'Gross Margin' as StatementLine, Sum(TransactionAmount) as TransactionAmount Resident Data where Match(StatementLine,'Sales','Cost of Sales') > 0 Group by Company;
Woah! You just opened my eyes to the logic you can write in the load editor. I was a software developer before I became a data analyst, so this is music to my ears! Lengthy? Yes. Very informative? Absolutely!