Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum if LOAD

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

1 Solution

Accepted Solutions
er_mohit
Master II
Master II

try this

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

View solution in original post

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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;

er_mohit
Master II
Master II

try this

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
MayilVahanan

Hi

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;

or

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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;