topic Re: Merging columns then counting values in QlikView

Merging columns then counting values

jstan — Wed, 25 Nov 2020 16:16:04 GMT

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 🙂

Re: Merging columns then counting values

edwin — Fri, 06 Nov 2020 13:21:24 GMT

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

Re: Merging columns then counting values

edwin — Fri, 06 Nov 2020 13:24:08 GMT

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;