# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

## [Help] I need help for make the create table

 Ceritification Table Name Table I want to create table or pivot in qlik Name Position Certification Name Determine how many certification by Name as like count-ifs in Excel A C A 0 1 >2 >3 A PG Y B 1 3 3 2 A SG Y C B SF Y D B PF Y E B C Y F B PG G B SG Y H C PG Y I C SG Y D SF Y D PF Y F C Y G PG Y H PG Y H SG Y H C Y H SF I SF Y I PF
1 Solution

Accepted Solutions
Valued Contributor

## Re: [Help] I need help for make the create table

Hi 정훈 최

I don't this this was a very easy exercise for me. I'm calling the artillery: stalwar1

I think I did it but I'm not sure if it is the right way. At least the numbers match. I struggled the most with Name 'E' as there are no certifications for it so it was null but you needed 0 in the final table at the bottom...

I hope there is a simple way

This is the Dimension (Row) for the pivot table although it's not really required:

='Summary'

This is the Dimension (Column) for the pivot table

=IF(ISNULL(AGGR(COUNT({<Certification={Y}>} Name),Name)),'0',

IF(AGGR(COUNT({<Certification={Y}>} Name),Name) = 1 ,'1',

IF(AGGR(COUNT({<Certification={Y}>} Name),Name) = 2 ,'2','3+')))

And this is the Measure:

Count((AGGR(COUNT(Name),Name)))

This is what I used to load data:

Name,Position,Certification

A,C,

A,PG,Y

A,SG,Y

B,SF,Y

B,PF,Y

B,C,Y

B,PG,

B,SG,Y

C,PG,Y

C,SG,Y

D,SF,Y

D,PF,Y

F,C,Y

G,PG,Y

H,PG,Y

H,SG,Y

H,C,Y

H,SF,

I,SF,Y

I,PF,

];

Name

A

B

C

D

E

F

G

H

I

];

12 Replies
Valued Contributor

## Re: [Help] I need help for make the create table

Hi 정훈 최

I don't this this was a very easy exercise for me. I'm calling the artillery: stalwar1

I think I did it but I'm not sure if it is the right way. At least the numbers match. I struggled the most with Name 'E' as there are no certifications for it so it was null but you needed 0 in the final table at the bottom...

I hope there is a simple way

This is the Dimension (Row) for the pivot table although it's not really required:

='Summary'

This is the Dimension (Column) for the pivot table

=IF(ISNULL(AGGR(COUNT({<Certification={Y}>} Name),Name)),'0',

IF(AGGR(COUNT({<Certification={Y}>} Name),Name) = 1 ,'1',

IF(AGGR(COUNT({<Certification={Y}>} Name),Name) = 2 ,'2','3+')))

And this is the Measure:

Count((AGGR(COUNT(Name),Name)))

This is what I used to load data:

Name,Position,Certification

A,C,

A,PG,Y

A,SG,Y

B,SF,Y

B,PF,Y

B,C,Y

B,PG,

B,SG,Y

C,PG,Y

C,SG,Y

D,SF,Y

D,PF,Y

F,C,Y

G,PG,Y

H,PG,Y

H,SG,Y

H,C,Y

H,SF,

I,SF,Y

I,PF,

];

Name

A

B

C

D

E

F

G

H

I

];

New Contributor III

## Re: [Help] I need help for make the create table

I'm not sure but I think these no name that doesn't have certification.My solution:

=Count(DISTINCT{<count = {0}>}Name)

=Count(DISTINCT{<count = {1}>}Name)

=Count(DISTINCT{<count = {2}>}Name)

=Count(DISTINCT{<count = {">=3"} >}Name)

data:

Name,

Position,

Certification,

if(isNull(Certification), 0, 1) as value

FROM (ooxml, embedded labels, header is 1 lines, table is Sheet1);

Left Join

Name,

sum(value) as count

Resident

data

Group by Name;

New Contributor III

## Re: [Help] I need help for make the create table

luismadriz‌ good job, I think your solution is correct if we should base on "Name Table"

MVP

## Re: [Help] I need help for make the create table

Another option

Dimension

=Aggr(

If(Count({<Certification={Y}>} Name) + Sum({1} 0) = 0, Dual('0', 1),

If(Count({<Certification={Y}>} Name) + Sum({1} 0) = 1, Dual('1', 2),

If(Count({<Certification={Y}>} Name) > 2, Dual('>2', 3), Dual('>3', 4)))), Name)

Expression

=Count(DISTINCT Name)

Valued Contributor

## Re: [Help] I need help for make the create table

Thanks Sunny and Mariusz for your replies,

I tried to let this go but I couldn't

Mariusz, I like your solution but I'm just assuming this is required in the front end

Sunny, I imagine you meant =2 instead of >2

I don't think we've got it right yet! The results seem fine based on the current data but if there were no Names with the number of certifications needed (0,1,2,3+) then the dimension wouldn't show. For example if E didn't exist we wouldn't have the category with '0'.

We need a fixed Dimension with this:

=ValueList('0','1','2','3+')

That way, regardless of the data there will always be those 4 categories. I mean please don't get me wrong, it's not that it was explicitly said on this post, I'm just making it a bit more fun!

And this is the measure that I could come up with:

=IF(ValueList('0','1','2','3+')='0',COUNT(DISTINCT Name) - COUNT(DISTINCT {<Certification={Y}>} Name),

IF(ValueList('0','1','2','3+')='1',SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) =1,1)),

IF(ValueList('0','1','2','3+')='2',SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) =2,1)),

SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) >2,1)))))

I still struggled to get the first one where certification was null so I had to work around it

This is what would happen if we didn't have E for example:

And this is what would happen if there were no Names with 3 or more for example:

Fun stuff!

Cheers,

Luis

MVP

## Re: [Help] I need help for make the create table

Luis -

Yes, you are right, missing data is going to create problems. But that can be overcome if the missing data is only because of set analysis.... I added Sum({1} 0) to force a 0 even if Count({<Certification={Y}>} Name) is non existent for a particular Name.

=Aggr(

If(Count({<Certification={Y}>} Name) + Sum({1} 0) = 0, Dual('0', 1),

If(Count({<Certification={Y}>} Name) + Sum({1} 0) = 1, Dual('1', 2),

If(Count({<Certification={Y}>} Name) > 2, Dual('>2', 3), Dual('>3', 4)))), Name)

Also, I will really appreciate if you can post your sample qvf as it will not just help me understand what you have done, but might help others also.

Best,

Sunny

Valued Contributor

## Re: [Help] I need help for make the create table

Cheers Sunny, that's what I thought the Sum({1} 0) was for but it doesn't work like that maybe because it's trying to add null with 0?. If I remove the E for example the Dimension '0' disappears. The only way that I've been able to do it is with the ValueList...

Se the results if I remove the E from the second table:

Not sure how can I post the QVF

The last pivot table (with ValueList) has this as a column dimension:

=ValueList('0','1','2','3+')

and has this as the measure:

=IF(ValueList('0','1','2','3+')='0',COUNT(DISTINCT Name) - COUNT(DISTINCT {<Certification={Y}>} Name),

IF(ValueList('0','1','2','3+')='1',SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) =1,1)),

IF(ValueList('0','1','2','3+')='2',SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) =2,1)),

SUM(TOTAL IF(AGGR(COUNT({<Certification={Y}>} Name),Name) >2,1)))))

Valued Contributor

## Re: [Help] I need help for make the create table

... and 정훈 최 hasn't said anything yet!!

choijunghoon

New Contributor