Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agrimroquette
Contributor II

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:-

EmployeeJanFebMarAprMayJunJulAugSepOctNovDec
Adeline HEUSELE222110171819-1-223232122
Alexandre DHELLEMME2322-11120-123242021
Alexandre FAVRE222219161416-4-518201816
Amandine SCOUMAQUE212123212022-1023241712
Amarish THAKAR2220221891010897124
Anthony LEMAI2121-15612121114152223
Antoine GOUVERNEUR2121242219210223232120

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)

1 Solution

Accepted Solutions
ahaahaaha
Honored Contributor

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

1.jpg

Example at attached file.

Regards,

Andrey

13 Replies

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

agrimroquette
Contributor II

Re: If condition

Hi Avinash,

Thanks for reply, attached is my sample data.

Re: If condition

Find the attachment for the solution

agrimroquette
Contributor II

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')

))

c1.png

effinty2112
Honored Contributor

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

ahaahaaha
Honored Contributor

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

1.jpg

Example at attached file.

Regards,

Andrey

effinty2112
Honored Contributor

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

agrimroquette
Contributor II

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

Community Browser