Hos to create a table with a column that count a number of entries with a string in common ? Aggr ?

[JulienCha]

Sorry for my english, i'm a beginner both with English and Qlik.

From my table, i would like to extract a new form of my table. I want to count the numer of 'Ticket' wich have a part of the text of 'Objet du Ticket' in common. Then, i also want to display a list of 'N° Ticket' that are associated.

Here is the example :

N° Ticket	Objet du ticket
1	TELEP_TAC_MOUNI_terou
2	COMPT_RTC_POULA_gadeu
3	COMPT_RTC_BIDOU_gende
4	ROR_PDH_CAILLU_juldij
5	ROR_PDH_BIDOU_judisl
6	COMPT_RTC_POULA_juhed
7	TELEP_TAC_MOUNI_hdysu
8	TELEP_TAC_MOUNI_ferdu
9	COMPT_RTC_CARLO_deuse
10	COMPT_RTC_CARLO_juede

 ==>

N° Ticket	Objet du ticket tronqué	Nombre de tickets
1;7;8	TELEP_TAC_MOUNI	3
2;6	COMPT_RTC_POULA	2
9;10	COMPT_RTC_CARLO	2
3	COMPT_RTC_BIDOU	1
4	ROR_PDH_CAILLU	1
5	ROR_PDH_BIDOU	1

Thank you

[JulienCha]

Sorry for my english, i'm a beginner both with English and Qlik.

From my table, i would like to extract a new form of my table. I want to count the numer of 'Ticket' wich have a part of the text of 'Objet du Ticket' in common. Then, i also want to display a list of 'N° Ticket' that are associated.

Here is the example :

N° Ticket	Objet du ticket
1	TELEP_TAC_MOUNI_terou
2	COMPT_RTC_POULA_gadeu
3	COMPT_RTC_BIDOU_gende
4	ROR_PDH_CAILLU_juldij
5	ROR_PDH_BIDOU_judisl
6	COMPT_RTC_POULA_juhed
7	TELEP_TAC_MOUNI_hdysu
8	TELEP_TAC_MOUNI_ferdu
9	COMPT_RTC_CARLO_deuse
10	COMPT_RTC_CARLO_juede

 ==>

N° Ticket	Objet du ticket tronqué	Nombre de tickets
1;7;8	TELEP_TAC_MOUNI	3
2;6	COMPT_RTC_POULA	2
9;10	COMPT_RTC_CARLO	2
3	COMPT_RTC_BIDOU	1
4	ROR_PDH_CAILLU	1
5	ROR_PDH_BIDOU	1

Thank you

[AdiPai]

Hey Julien ,

You can try this using the concat function - 

Thanks

Adi

[Ksrinivasan]

HI,

YES,  CONCAT(Ticket_Num),';')   he wants in his result separated by ;,

[JulienCha]

And how can I count the number of [N°ticket]  with identical Objet du ticket tronqué ?

Where [Objet du ticket tronqué]  is not a defined dimension but a formula in a dimension wich is :

If(Wildmatch([Objet du ticket],'*_*') And Wildmatch([Objet du ticket],'*-*'),Left([Objet du ticket],index([Objet du ticket],'-',3)-1),
If (Wildmatch([Objet du ticket],'*_*'),(Left([Objet du ticket],index([Objet du ticket],'_',3)-1)),(Left([Objet du ticket],index([Objet du ticket],'-',3)-1)))

[AdiPai]

@JulienCha  - it would be better if you can create this as a new feild in the script than write a caclulated dimension ..  i guess just removing the 6 right chars from Objet du ticket should give you  Objet du ticket tronqué

ex - LEFT(TELEP_TAC_MOUNI_hdysu,15) will give you = TELEP_TAC_MOUNI

[RayArina]

Hi @JulienCha 

Try this

Dimension - left (PurgeChar(Objet_du_ticket, SubField(Objet_du_ticket, '_', -1)), len(PurgeChar(Objet_du_ticket, SubField(Objet_du_ticket, '_', -1)))-1)

Expression 1 - Concat([N°Ticket], ';')

Expression 2 - count([N°Ticket])