Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I want to count how many time each resource are used per Item content.
What I did first is merging three fields ressource1 , ressource2 & Ressource 3 into one unique field called "Ressource" in a new table as following:
[Table1]:
LOAD
[Title],
[ID],
[Item Content],
[Start date],
[End date],
[Ressource1],
[Ressource2],
[Ressource3],
[Color],
[Group],
[Item Type]
FROM [lib://AttachedFiles/TEST TIMELINE QLICKSENSE.xlsx]
(ooxml, embedded labels, table is Table1);
[Table2]:
Load
[Ressource1] AS [Ressource]
resident Table1;
Concatenate(Table2)
load
[Ressource2] AS [Ressource]
RESIDENT Table1;
Concatenate(Table2)
Load
[Ressource3] AS [Ressource]
RESIDENT Table1;
Then I compare the "Ressource" field to the "Item Content" field. But at this step, each resource is counted at least once time per project and it's not what I expect as results.
What I want to see is :
Resource , Item content
AB=0, Pilot
AB=1, Pilot2
AB=0, Pilot3
AB=0, New process
ALPHA=1, Pilot
ALPHA=0, Pilot2
ALPHA=1, Pilot3
ALPHA=0, New Process
Thx for your reply 🙂
first you need to identify your key, normally i would say Item Content + a date. from what i see you just need to see is a resource was ever a Pilot, Pilot2, or Pilot3. what you were doing can work if you added Item Concent:
[Table2]: Load ItemContent, [Ressource1] AS [Ressource] resident Table1;
Concatenate(Table2) load ItemContent, [Ressource2] AS [Ressource] RESIDENT Table1;
Concatenate(Table2) Load [Ressource3] AS [Ressource] RESIDENT Table1;
this way you have the new table related to your original table in a 1 to many relationship.
there is another way to unpivot your table:
https://community.qlik.com/t5/New-to-QlikView/Unpivot-A-Source-Crosstab-Table/m-p/684081
first you need to identify your key, normally i would say Item Content + a date. from what i see you just need to see is a resource was ever a Pilot, Pilot2, or Pilot3. what you were doing can work if you added Item Concent:
[Table2]: Load ItemContent, [Ressource1] AS [Ressource] resident Table1;
Concatenate(Table2) load ItemContent, [Ressource2] AS [Ressource] RESIDENT Table1;
Concatenate(Table2) Load [Ressource3] AS [Ressource] RESIDENT Table1;
this way you have the new table related to your original table in a 1 to many relationship.
there is another way to unpivot your table:
https://community.qlik.com/t5/New-to-QlikView/Unpivot-A-Source-Crosstab-Table/m-p/684081
to add on to this, in case the same resource was used as an itemContent multiple times, you may want to aggregate your table2:
NewTable2: load itemContent, Resource, count(Resource) as Count resident table2 group by itemContent, Resource;
drop table Table2;