topic Re: Total sum calculation using group by in QlikView

Total sum calculation using group by

Fri, 10 Jan 2014 14:50:44 GMT

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.

Re: Total sum calculation using group by

Nicole-Smith — Fri, 10 Jan 2014 14:55:39 GMT

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).

Re: Total sum calculation using group by

Fri, 10 Jan 2014 15:15:28 GMT

Thank you. It worked.

Re: Total sum calculation using group by

Nicole-Smith — Fri, 10 Jan 2014 15:24:27 GMT

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