Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
gagewhite
Contributor III
Contributor III

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

Hello friends!

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

CompanyStatement LineTransaction Amount
ASales150
ACost of Sales100

 

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

CompanyStatement LineTransaction Amount
ASales150
ACost of Sales100
AGross Margin50

 

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!

Labels (4)
3 Replies
jwjackso
Specialist II
Specialist II

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;

 

 

Capture.JPG

attached qvf file

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
gagewhite
Contributor III
Contributor III
Author

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