Count a field with same part of a text and group it

[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

[jyothish8807]

Hi JukienCha,

Try this:

Tab1:
load * Inline [
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
];

NewTable:
Load
left([Objet du ticket],index([Objet du ticket],'_',3)-1) as [Objet du ticket tronqué],
Concat([N° Ticket],',') as [N° Ticket_New]
resident Tab1
group by left([Objet du ticket],index([Objet du ticket],'_',3)-1);

Drop table Tab1;

On front end you can create a measure = Count([Objet du ticket tronqué])

[JulienCha]

The table I showed as an example is much smaller than my real table. My real table as 6000 lines...

So i guess i wont be able to use your solution.

[jyothish8807]

The solution will work for n no of rows.

[JulienCha]

I'm sorry, it does not work. When I create a new object "Table" in Qlik Sense, I must entre a dimension.