topic Re: Sum if LOAD in QlikView

Sum if LOAD

Wed, 17 Jul 2013 12:47:21 GMT

Hi Guys

The attached is an image of a table I loaded called QT.

How coudl i get this:

Type | QT_NUM | Value

AV 254429 xyz

where xyz is result of:

(If CATEG = 0 then sum QT_CHARGE+QT_MARKUP+QT_ADJUST) + (if CATEG <>0 then sum QT_MARKUP+QT_ADJUST)

Thanks

Re: Sum if LOAD

kaushiknsolanki — Wed, 17 Jul 2013 12:51:04 GMT

Hi,

Try this.

Load Type ,

QT_NUM ,

Sum(if(CATEG = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST)) as Sum

From xyz.

Regards,

Kaushik Solanki

Re: Sum if LOAD

Wed, 17 Jul 2013 12:55:32 GMT

Hi Kaushik

Sorry, but I get an invalid expression:

Script:

TEST:

NoConcatenate

LOAD

QT_NUMBER as JobNum,

Sum(if(Categ = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST)) as Value

Resident QT;

exit script;

Re: Sum if LOAD

er_mohit — Wed, 17 Jul 2013 12:59:37 GMT

try this

if(CATEG <> '0', QT_MARKUP+QT_ADJUST,QT_CHARGE+QT_MARKUP+QT_ADJUST)) as Sum

Re: Sum if LOAD

kaushiknsolanki — Wed, 17 Jul 2013 13:01:23 GMT

Ohh I m sorry i forgot to give group by .

Try this.

Load Type ,

QT_NUM ,

Sum(if(CATEG = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST)) as Sum

From xyz Group by Type,QT_NUM ;

Regards,

Kaushik Solanki

Re: Sum if LOAD

MayilVahanan — Wed, 17 Jul 2013 13:04:32 GMT

Try like this

Try this.

Load Type ,

QT_NUM ,

Sum(if(CATEG = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST)) as Value

From xyz

Group by Type, QT_NUM;

Try this.

Load Type ,

QT_NUM ,

if(CATEG = 0,QT_CHARGE+QT_MARKUP+QT_ADJUST, QT_MARKUP+QT_ADJUST) as Value

From xyz;

Then in expression =sum(Value)

Hope it helps

Re: Sum if LOAD

Wed, 17 Jul 2013 13:14:28 GMT

Thanks the Group by Fixed it, BUT

I would like only the QT_NUMBER as JobNum and Value. How do you sum the nested If statements?

TEST:

NoConcatenate

LOAD

QT_NUMBER as JobNum,

Sum(

If(Categ=0,Sum(QT_CHARGE+QT_MARKUP+QT_ADJUST),

If(Categ=1,Sum(QT_MARKUP+QT_ADJUST),

If(Categ=3, Sum(QT_MARKUP+QT_ADJUST))))

) as Value

Resident QT

Group By QT_NUMBER,Categ;