if condition

[#1]

Hi Experts,

I have the following tables.

SALES:

LOAD Date,

          Branch,

    SUM(AMT) AS AMT

FROM

[\QVD\NewSales.qvd]

(qvd)

Group BY Branch, Date;

LEFT JOIN

TARGET:

LOAD Branch,

        EndDate as Date

FROM

[\TARGETS.xlsx]

(ooxml, embedded labels, table is TARGETS)

Group BY Branch, EndDate;

from the above table, i need to calculate points for branch which is based on sum(AMT)/sum(Target)

i.e., if sum(AMT)/sum(Target) is less than 50%, points=0

      if sum(AMT)/sum(Target) is between 50% and 100%, points=1

     if sum(AMT)/sum(Target) is above 100%, points = 3

i m trying the below script, but i m getting error as invalid expression while loading script.

SALE_POINTS:

LOAD *,

IF(sum(AMT)/sum(Target)<='0.5','0',

IF(sum(AMT)/sum(Target)>'0.5' and sum(AMT)/sum(Target)<= '1','1',

IF(sum(AMT)/sum(Target) >'1','3','0'))) as POINTS_FS

Resident SALES;

DROP Table SALES;

Please help me to correct the script.

[sasikanth]

HI,

Try with alias names

IF(AMT/TARGET<.......,,

...

....)

[tresesco]

Missing  Group By.

[Anonymous]

Sum() is aggregate function, cant use like you did:

Try this one:

IF((AMT/Target)<='0.5','0',

IF((AMT/Target)>'0.5' and (AMT/Target)<= '1','1',

IF((AMT/Target) >'1','3','0'))) as POINTS_FS

Resident SALES;

DROP Table SALES;

[Kushal_Chawda]

you need to do group by Remaining Fields as you are using aggregation fucntion

try

SALE_POINTS:

LOAD *,

IF(sum(AMT)/sum(Target)<='0.5','0',

IF(sum(AMT)/sum(Target)>'0.5' and sum(AMT)/sum(Target)<= '1','1',

IF(sum(AMT)/sum(Target) >'1','3','0'))) as POINTS_FS

Resident SALES

group by Date, Branch;

DROP Table SALES;

[jagan]

Hi,

If you Sum() in the script then you have use Group other wise you will get error, try scirpt below

SALE_POINTS:

LOAD Date, Branch,

IF(sum(AMT)/sum(Target)<='0.5','0',

IF(sum(AMT)/sum(Target)>'0.5' and sum(AMT)/sum(Target)<= '1','1',

IF(sum(AMT)/sum(Target) >'1','3','0'))) as POINTS_FS

Resident SALES

group by Date, Branch;

DROP Table SALES;

Hope this helps you.

Regards,

Jagan.

[MK_QSL]

SALES:

Load Branch, Date, SUM(AMT) as AMT

From TableName

Group By Branch, Date

LEFT JOIN (SALES)

Load Branch, EndDate as Date, SUM(Target) as Target

From TableName

Group By Branch, EndDate

Final:

Load

  Branch,

  Date,

  SUM(AMT)/SUM(Target) as Achieved,

  If((SUM(AMT)/SUM(Target)) >= 1, 3, If((SUM(AMT)/SUM(Target)) >= 0.5, 1, 0)) as Flag_Branch_Date

Resident SALES

Group By Branch, Date;

Left Join (Final)

Load Branch, If((SUM(AMT)/SUM(Target)) >= 1, 3, If((SUM(AMT)/SUM(Target)) >= 0.5, 1, 0)) as Flag_Branch

Resident SALES

Group By Branch;

Drop Table SALES;

[tamilarasu]

Something like this

SALE_POINTS:

LOAD *,

IF((sum(AMT)/sum(Target))<=0.5,0,

IF((sum(AMT)/sum(Target))>0.5 and (sum(AMT)/sum(Target))<= 1,1,

IF(sum(AMT)/sum(Target) >1,3,0))) as POINTS_FS

Resident SALES

Group By Date, Branch;

DROP Table SALES;

[Mark_Little]

Hi,

I would agree with Balraj, As it looks like you already have Sum Values you are using on your original loads.

Unless you are just wanting one values pre branch and not monthly?

Then you would want to add group by Branch, to what you currently have.for POINTS_FS

Mark