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

[gagewhite]

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!

[jwjackso]

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;

[Taoufiq_Zarra]

@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

[gagewhite]

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