topic ? in QlikView

?

xyz1 — Sun, 14 Apr 2019 15:34:31 GMT

Re: If condition

avinashelite — Fri, 28 Jul 2017 07:02:06 GMT

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

?

xyz1 — Sun, 14 Apr 2019 15:35:38 GMT

Re: If condition

avinashelite — Fri, 28 Jul 2017 07:20:30 GMT

Find the attachment for the solution

?

xyz1 — Sun, 14 Apr 2019 15:36:25 GMT

Re: If condition

avinashelite — Fri, 28 Jul 2017 07:27:40 GMT

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

effinty2112 — Fri, 28 Jul 2017 07:42:22 GMT

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

ahaahaaha — Fri, 28 Jul 2017 07:47:59 GMT

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

effinty2112 — Fri, 28 Jul 2017 07:51:38 GMT

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

?

xyz1 — Sun, 14 Apr 2019 15:38:24 GMT

Re: If condition

effinty2112 — Fri, 28 Jul 2017 09:22:42 GMT

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