Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try this
if(CATEG <> '0', QT_MARKUP+QT_ADJUST,QT_CHARGE+QT_MARKUP+QT_ADJUST)) as Sum
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
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;
try this
if(CATEG <> '0', QT_MARKUP+QT_ADJUST,QT_CHARGE+QT_MARKUP+QT_ADJUST)) as Sum
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
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 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;