13 Replies Latest reply: Jul 28, 2017 6:04 AM by Agrim Sharma

# If condition

Hi,

>I want to have count “Employee” who have all values equal to ‘0’ from Jan-Dec as ‘Allocated’

>Count “Employee” who have all values less than ‘0’ from Jan-Dec as ‘Over allocated’

And

>Count “Employee” who have all values Greater than ‘0’ from Jan-Dec as ‘Available’

I applied this formula but am not getting desired results

= IF((Value3) = '0' , 'Allocated', IF((Value3) < '0' , 'Over Allocated', if((Value3) > '0' , 'Available' )))

Below is my data sample:-

 Employee Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Adeline HEUSELE 22 21 10 17 18 19 -1 -2 23 23 21 22 Alexandre DHELLEMME 23 22 -1 1 1 2 0 -1 23 24 20 21 Alexandre FAVRE 22 22 19 16 14 16 -4 -5 18 20 18 16 Amandine SCOUMAQUE 21 21 23 21 20 22 -1 0 23 24 17 12 Amarish THAKAR 22 20 22 18 9 10 10 8 9 7 12 4 Anthony LEMAI 21 21 -1 5 6 12 12 11 14 15 22 23 Antoine GOUVERNEUR 21 21 24 22 19 21 0 2 23 23 21 20

And desired result is as follows:-

Allocated=0(no Employee has all values '0' from Jan-Dec)

Over Allocated=5(Adeline HEUSELE, Alexandre DHELLEMME, Alexandre FAVRE, Amandine SCOUMAQUE, Anthony LEMAI)

Available=2(Amarish THAKAR, Antoine GOUVERNEUR)

• ###### Re: If condition

Could you share the sample data set for this ? how is the data jan , feb are all coming from different columns are in the same columns ..share the sample data will help you

• ###### Re: If condition

Hi Avinash,

Thanks for reply, attached is my sample data.

• ###### Re: If condition

Find the attachment for the solution

• ###### Re: If condition

Hi Avinash,

i am using Qlik sense, can you send me the formula in writing here?

regards

• ###### Re: If condition

Temp:

LOAD Employee,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

\\Msad\root\AP\APBO\MI\users\ravinash\Downloads\Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

CrossTable(Month,value)

LOAD Employee,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

\\Msad\root\AP\APBO\MI\users\ravinash\Downloads\Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

Expression :

if(count({<value={0}>}value)=12,'Allocated',if(count({<value={"<0"}>}value)>=1,'Over Allocated',

if(count({<value={">0"}>}value)>1,'Avilable')

))

• ###### Re: If condition

Hi Agrim,

You say:

>Count “Employee” who have all values less than ‘0’ from Jan-Dec as ‘Over allocated’

but no employees have all values less than zero, did you mean

>Count “Employee” who have some values less than ‘0’ from Jan-Dec as ‘Over allocated’

?

Regards

Andrew

• ###### Re: If condition

yes exactly this i want,

i want to check from jan to dec for all employee, if any value less than 0 from jan to dec i want it as over allocated employee but if all value greater than 0 than employee should be Available and if all the from jan to dec is 0 than it should be allocated

• ###### Re: If condition

Hi,

May be like this?

Table1:

LOAD Employee,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

[https://community.qlik.com/thread/269289?sr=inbox&ru=232295]

(html, codepage is 1251, embedded labels, table is @1);

Table2:

CrossTable(Month, Value)

LOAD*

Resident Table1;

//check the first condition

Table3:

LOAD

Employee,

If(Count(Value)=0, 'Allocated') as Allocated

Resident Table2

Where Value<>0

Group By Employee;

//check the second condition

Table4:

LOAD

Employee,

If(Count(Value)>0, 'Over Allocated') as OverAllocated

Resident Table2

Where Value<0

Group By Employee;

//check the third condition

Table5:

LOAD

Employee,

If(Count(Value)=12, 'Available') as Available

Resident Table2

Where Value>=0

Group By Employee;

//attached the results to the main table

Left Join (Table1)

LOAD*

Resident Table3;

Left Join (Table1)

LOAD*

Resident Table4;

Left Join (Table1)

LOAD*

Resident Table5;

DROP Tables Table2, Table3, Table4, Table5;

Result

Example at attached file.

Regards,

Andrey

• ###### Re: If condition

my script is like this, your logic is right, how can i use that in this script?

Temp3:

CrossTable( Month, Value3,1)

LOAD

"Employee",

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM [lib://rp/OCC_Resource Planning.xlsx]

(ooxml, embedded labels, table is Availability);

MappingTable4:

Mapping

LOAD

[Employee]&Month(Date#(Month, 'MMM')),

Value3

Resident Temp3;

• ###### Re: If condition

Thanks a lot Andrey, can you plese tell me why you used '12' for If(Count(Value)=12, 'Available') as Available ?

• ###### Re: If condition

Hi Agrim,

Try:

Data:

CrossTable(Month,Value)

LOAD * INLINE [

Employee, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

Adeline HEUSELE, 22, 21, 10, 17, 18, 19, -1, -2, 23, 23, 21, 22

Alexandre DHELLEMME, 23, 22, -1, 1, 1, 2, 0, -1, 23, 24, 20, 21

Alexandre FAVRE, 22, 22, 19, 16, 14, 16, -4, -5, 18, 20, 18, 16

Amandine SCOUMAQUE, 21, 21, 23, 21, 20, 22, -1, 0, 23, 24, 17, 12

Amarish THAKAR, 22, 20, 22, 18, 9, 10, 10, 8, 9, 7, 12, 4

Anthony LEMAI, 21, 21, -1, 5, 6, 12, 12, 11, 14, 15, 22, 23

Antoine GOUVERNEUR, 21, 21, 24, 22, 19, 21, 0, 2, 23, 23, 21, 20

];

then

Count Allocated =count(DISTINCT {\$<Employee = E({<Value -= {0}>})>}Employee)

Count Over Allocated =count(DISTINCT {\$<Value = {"<0"}>} Employee)

Count Available =count(DISTINCT {\$<Employee = E({<Value = {"<0"}>})>} Employee)

Allocated Employees =concat(DISTINCT {\$<Employee = E({<Value -= {0}>})>}Employee,', ')

Over Allocated Employees  =concat(DISTINCT {\$<Value = {"<0"}>} Employee,', ')

Available Employees  =concat(DISTINCT {\$<Employee = E({<Value = {"<0"}>})>} Employee,', ')

Regards

Andrew

• ###### Re: If condition

this is my script, can you please help on this

Temp3:

CrossTable( Month, Value3,1)

LOAD

"Employee",

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM [lib://rp/OCC_Resource Planning v1.5a.xlsx]

(ooxml, embedded labels, table is Availability);

MappingTable4:

Mapping

LOAD

[Employee]&Month(Date#(Month, 'MMM')),

Value3

Resident Temp3;

• ###### Re: If condition

Hi Agrim,

Using your sample spreadsheet you only need this script:

Data:

CrossTable(Month,Value)

LOAD *

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

Then these expressions in the front end:

Count Allocated =count(DISTINCT {\$<Employee = E({<Value -= {0}>})>}Employee)

Count Over Allocated =count(DISTINCT {\$<Value = {"<0"}>} Employee)

Count Available =count(DISTINCT {\$<Employee = E({<Value = {"<0"}>})>} Employee)

Allocated Employees =concat(DISTINCT {\$<Employee = E({<Value -= {0}>})>}Employee,', ')

Over Allocated Employees  =concat(DISTINCT {\$<Value = {"<0"}>} Employee,', ')

Available Employees  =concat(DISTINCT {\$<Employee = E({<Value = {"<0"}>})>} Employee,', ')

Regards

Andrew