Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sibrulotte
Creator III
Creator III

In the Script: What is the equivalent of Total of a chart

Hi,

I have a simple table.

Issuer number, cost

ABC          ,   100

DEF          ,  300

I want to create a third field that is Cost/total cost,

ABC would be 100/400

DEF would be 300/400

How do I get my 400?


My script as it stands, but obviously not working...
sum(VAL_RPT.COST) as  R_OPPQC_00020_COST,
Sum(Total (VAL_RPT.COST)) as  R_OPPQC_00020_Total,
VAL_RPT.ISSUER_NO
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
group by VAL_RPT.ISSUER_NO
;

1 Solution

Accepted Solutions
sibrulotte
Creator III
Creator III
Author

Here's how I did it:

load
sum(if(Match (VAL_RPT.PORT_TYPE, 'MI','ME'),VAL_RPT.COST)) as  R_OPPQC_00020_COST,
VAL_RPT.ISSUER_NO as R_OPPQC_00020.ISSUER_NO
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
group by VAL_RPT.ISSUER_NO
;


R_OPPQC_00020_total:
left join (R_OPPQC_00020)
load
sum(VAL_RPT.COST) as  R_OPPQC_00020
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
;

View solution in original post

2 Replies
sibrulotte
Creator III
Creator III
Author

Here's how I did it:

load
sum(if(Match (VAL_RPT.PORT_TYPE, 'MI','ME'),VAL_RPT.COST)) as  R_OPPQC_00020_COST,
VAL_RPT.ISSUER_NO as R_OPPQC_00020.ISSUER_NO
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
group by VAL_RPT.ISSUER_NO
;


R_OPPQC_00020_total:
left join (R_OPPQC_00020)
load
sum(VAL_RPT.COST) as  R_OPPQC_00020
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
;

sibrulotte
Creator III
Creator III
Author

And YES, I mark my responses as correct if I come up with a valid solution, lol!!!