Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create total based on another critera.

med:

LOAD

MEM#,

//MEM# as Med_MEM#,

//MEM# as Medd,

ENCOUNTER#,

     '3rd Quarter 2011' as Period, //Create a column calls period and populate it with the 3rd text //

     '261740' AS MM,

     '1.26' AS ACG,

     [VEND NAM],

     [TAX ID],

     [TOTAL PAID] as Total,

     SERVICE,

     DISCHARGE,

     LOS,

     [ICD9 DIAG],

     [FORM TYPE],

     IF([FORM TYPE]='HCFA 1450','H',If([FORM TYPE]='HCFA 1500','P',If([FORM TYPE]='NARRATIVE',' ','N'))) AS FORMID

FROM

Data\Dimensions\med.qvd

(qvd);

I would like to the Total to be calculate based on FORMID. Ex: If FORMID= H then Total it is Total_H. If FORMID=P then Total is Total_P and etc.

How can I do this?

Thanks.

2 Replies
Not applicable
Author

Do you need the Total fields to be separate or should there be one Total field, with the total based on FORMID?

For one total field, I would use:

MedIDTotals:

Load FORMID, Sum(Total) As IDTotal

Resident Med

Group By FORMID;


Using that, each FORMID will have an IDTotal associated with it. You could then map that value back to the main table using a Mapping Table or even combining the two using a join.

Anonymous
Not applicable
Author

I would like the Total fields to be separated and group by MEM# . thanks.