Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

choijunghoon
New Contributor

[Help] I need help for make the create table

Please help me.

I am beginner  so, i need your help please

Untitled.jpg

Ceritification TableName TableI want to create table or pivot in qlik
NamePositionCertificationNameDetermine how many certification by Name as like count-ifs in Excel
ACA01>2>3
APGYB1332
ASGYC
BSFYD
BPFYE
BCYF
BPGG
BSGYH
CPGYI
CSGY
DSFY
DPFY
FCY
GPGY
HPGY
HSGY
HCY
HSF
ISFY
IPF
1 Solution

Accepted Solutions
luismadriz
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

Untitled.png

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:

LOAD * INLINE [

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,

];

LOAD * INLINE [

Name

A

B

C

D

E

F

G

H

I

];

12 Replies
luismadriz
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

Untitled.png

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:

LOAD * INLINE [

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,

];

LOAD * INLINE [

Name

A

B

C

D

E

F

G

H

I

];

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

book7.png

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

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

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

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

data:

LOAD

Name,

Position,

Certification,

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

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



Left Join

LOAD

Name,

sum(value) as count

Resident

data

Group by Name;

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

luismadriz
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

Untitled.png

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

Untitled.png

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

Untitled.png

Fun stuff!

Cheers,

Luis

MVP
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

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

Untitled.png

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

luismadriz
Valued Contributor

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

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

choijunghoon

choijunghoon
New Contributor

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

Wow!! So many answer about this ~

Thank you so So so So much  everyone who reply my question.