Skip to main content

App Development

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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 III
Specialist III

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