topic Re: Adding 1 Measure Based Off 2 Values in a Single Dimension (add a row of data) in App Development

Adding 1 Measure Based Off 2 Values in a Single Dimension (add a row of data)

gagewhite — Sat, 16 Nov 2024 18:31:56 GMT

Hello friends!

I am working with some financial data. Here is what it looks like:

Company	Statement Line	Transaction Amount
A	Sales	150
A	Cost of Sales	100

I am trying to calculate gross margin and have it appear in the same column as the statement lines I currently have:

Company	Statement Line	Transaction Amount
A	Sales	150
A	Cost of Sales	100
A	Gross Margin	50

Is there anyway to do this in the data load editor? I need to have the calculated Statement Lines and the raw data statement lines appear in the same columns.

I feel like this can't be too difficult, maybe I'm just having an off day. Thanks in advance!

Re: Adding 1 Measure Based Off 2 Values in a Single Dimension (add a row of data)

jwjackso — Thu, 04 Jun 2020 19:22:11 GMT

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;

Re: Adding 1 Measure Based Off 2 Values in a Single Dimension (add a row of data)

Taoufiq_Zarra — Thu, 04 Jun 2020 20:10:55 GMT

@gagewhite

It's a really long script, I know, but I I wanted to test a different logic.. 😁

Info: LOAD * Inline [ Company,Statement Line,Transaction Amount A,Sales,150 A,Cost of Sales,100 ]; DATA: generic load Company,[Statement Line],[Transaction Amount] Resident Info; CombinedGenericTable: Load distinct Company Resident Info; drop Table Info; FOR i = NoOfTables()-1 to 0 STEP -1 LET vTable=TableName($(i)); //trace $(i) - '$(vTable)'; IF WildMatch('$(vTable)', 'DATA.*') THEN LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)]; DROP TABLE [$(vTable)]; ENDIF NEXT i outputtmp: noconcatenate load *,(Sales-[Cost of Sales]) as [Gross Margin] Resident CombinedGenericTable; drop table CombinedGenericTable; output: CrossTable([Statement Line], [Transaction Amount]) load * Resident outputtmp; ; drop table outputtmp;

attached qvf file

Re: Adding 1 Measure Based Off 2 Values in a Single Dimension (add a row of data)

gagewhite — Wed, 10 Jun 2020 13:21:00 GMT

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!

Thanks!

Gage