topic Create total based on another critera. in QlikView

Create total based on another critera.

Anonymous — Fri, 03 Jun 2011 15:26:16 GMT

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.

Re: Create total based on another critera.

Fri, 03 Jun 2011 18:10:08 GMT

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.

Create total based on another critera.

Anonymous — Fri, 03 Jun 2011 19:12:39 GMT

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