column name based on count

[#1]

Hi guys

I need to build a report on the frequency of receiving services:

Data:     

clientID	serviceName	serviceKey
1111	A	1
1111	A	2
1111	A	3
1111	B	4
1111	B	5
1116	B	6
1117	A	7
1118	A	8
1119	A	9
1119	A	10
1119	A	11
1119	A	12
1119	D	13
1119	D	14
1119	D	15
1126	D	16
1127	D	17
1128	A	18
1128	A	19
1128	A	20
1128	A	21

I need to count the number of times each client has received a service and to present it in the table below

Result:

	once	twice	3 times	4 and more
A	2		1	2
B		1
D	2		1

Thanks

[maksim_senin · Accepted solution]

Hi,

In general you should decide whether you want to calc. in on-the-fly or calc it in load script. A general idea fo load sript:

1. 1st you should load distinct client list with counted times say in a field Counter

2. then you need to get min and max no. of times

3. make a cycle for i = min to max in which join to your client list records with Counter = i and add a field (once, twice, etc) with necessary value

If you want to calc that in the runtime, you'll definitely need calc. dimension which is not a best choice in a large amount of data.

Best regards,

Maxim

[MK_QSL]

Create a Pivot Table

Dimension

serviceName

Expression

Once

=SUM(Aggr(IF(COUNT(serviceName)=1,1),clientID,serviceName))

Twice

=SUM(Aggr(IF(COUNT(serviceName)=2,1),clientID,serviceName))

3 Times

=SUM(Aggr(IF(COUNT(serviceName)=3,1),clientID,serviceName))

4 Times

=SUM(Aggr(IF(COUNT(serviceName)>3,1),clientID,serviceName))

File enclosed for your reference....

[#4]

Maxim, thank you for you answer

Yes, I prefer to load it in script. But I have an issue with date, because I need to filter by period. Actually there are clients table, services table and calendar in my model. If I understand correctly, your solution doesn't relate to the service date.

Any ideas?

[#5]

Thank you, it works well

[MK_QSL]

Glad to help you. Please don't forget to close the thread by selecting appropriate answer.

[#7]

Your solution is great but I'm looking for script solution, because it works very slow

[MK_QSL]

Data:
LOAD clientID, 
     serviceName, 
     serviceKey
FROM
[172423.xlsx]
(ooxml, embedded labels, table is Sheet1);


Temp:
Load 
  clientID,
  serviceName,
    Count(serviceName) as Flag 
Resident Data
Group By clientID,serviceName;

Now create a Straight or Pivot Table

Dimension

serviceName

Expressions

COUNT({<Flag = {1}>}Flag)

COUNT({<Flag = {2}>}Flag)

COUNT({<Flag = {3}>}Flag)

COUNT({<Flag = {">3"}>}Flag)

[#9]

Thank you.

I want to show the number of times according to the selected period (the excel file has column date). So the number of times is dynamic data (according to period). The script doesn't solve it unfortunatly. Do you have any idea?