Create total based on another critera.

[Anonymous]

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]

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]

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