topic Re: column name based on count in QlikView

column name based on count

Tue, 14 Jul 2015 06:06:40 GMT

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

once

twice

3 times

4 and more

Thanks

Re: column name based on count

maksim_senin — Tue, 14 Jul 2015 06:27:53 GMT

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

Re: column name based on count

MK_QSL — Tue, 14 Jul 2015 06:29:10 GMT

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

Re: column name based on count

Tue, 14 Jul 2015 06:58:58 GMT

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?

Re: column name based on count

Tue, 14 Jul 2015 07:07:25 GMT

Thank you, it works well

Re: column name based on count

MK_QSL — Tue, 14 Jul 2015 07:26:16 GMT

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

Re: column name based on count

Tue, 14 Jul 2015 08:46:59 GMT

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

Re: column name based on count

MK_QSL — Tue, 14 Jul 2015 09:07:42 GMT

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)

Re: column name based on count

Wed, 15 Jul 2015 07:47:27 GMT

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?