Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please help me.
I am beginner so, i need your help please
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 |
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:
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
];
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:
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
];
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:
LOAD
Name,
Position,
Certification,
if(isNull(Certification), 0, 1) as value
FROM
Left Join
LOAD
Name,
sum(value) as count
Resident
data
Group by Name;
luismadriz good job, I think your solution is correct if we should base on "Name 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)
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
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
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)))))
... and 정훈 최 hasn't said anything yet!!
Wow!! So many answer about this ~
Thank you so So so So much everyone who reply my question.