Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
1 Solution

Accepted Solutions
ahaahaaha
Partner - Master
Partner - Master

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

View solution in original post

10 Replies
avinashelite

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
Creator III
Creator III
Author

.

avinashelite

Find the attachment for the solution

xyz1
Creator III
Creator III
Author

.

avinashelite

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

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
Partner - Master
Partner - Master

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

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
Creator III
Creator III
Author

.