Total sum calculation using group by

[#1]

I am a new user with Qlikview.  I have several conversions I would like to perform within the load script using an excel file.  I need to extract medication dose units  and convert them  into one standard unit of measure (grams).  My data comes in mg, mg/kg, and grams.

I was somewhat successful to build this as an expression; however, the number of records that need to be converted are several thousand rows. It would much more efficient to do the conversion in the load script.

First, I assigned a new column for each conversation.  Want I need is the sum of all of the rows for each conversion, so I am only working with grams in my application. I have read about the Group by function; however, I just cannot get it to work.  I keep getting an invalid expression.

Attached is a sample load script.  I would greatly appreciate any help.

[Nicole-Smith · Accepted solution]

Try this:

Calc:

LOAD

    Name,

    MRN,

    Antibiotic,

    Dose,

    Drug_Unit,

    DQ,

    Num(if(Drug_Unit='mg', Dose / 1000, 0)) as  mgGm,

    Num(if(Drug_Unit='mg/kg',(DQ * 5) / 1000, 0) ) as  mgKgGm,

    Num(if(Drug_Unit='g',Dose,0)) as Grams  

Resident MAR;

ConvertTotals:

Load    Antibiotic,

        sum(mgGm) + sum(mgKgGm)+ sum(Grams) as Gram_total      

Resident Calc

Group By Antibiotic;

Since you're grouping by Antibiotic, that's the only other field you can load in (other than your sums).

[#3]

Thank you. It worked.

[Nicole-Smith]

Please mark as correct if it works for you so others can find answers to similar problems