topic Re: if condition in QlikView

if condition

Thu, 05 Nov 2015 07:23:13 GMT

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.

Re: if condition

sasikanth — Thu, 05 Nov 2015 07:26:01 GMT

HI,

Try with alias names

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

...

....)

Re: if condition

tresB — Thu, 05 Nov 2015 07:29:05 GMT

Missing Group By.

Re: if condition

Anonymous — Thu, 05 Nov 2015 07:30:09 GMT

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;

Re: if condition

Kushal_Chawda — Thu, 05 Nov 2015 07:31:04 GMT

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;

Re: if condition

jagan — Thu, 05 Nov 2015 07:36:11 GMT

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.

Re: if condition

MK_QSL — Thu, 05 Nov 2015 07:39:17 GMT

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;

Re: if condition

tamilarasu — Thu, 05 Nov 2015 07:41:49 GMT

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;

Re: if condition

Mark_Little — Thu, 05 Nov 2015 09:03:31 GMT

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