Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

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

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° TicketObjet du ticket
1TELEP_TAC_MOUNI_terou
2COMPT_RTC_POULA_gadeu
3COMPT_RTC_BIDOU_gende
4ROR_PDH_CAILLU_juldij
5ROR_PDH_BIDOU_judisl
6COMPT_RTC_POULA_juhed
7TELEP_TAC_MOUNI_hdysu
8TELEP_TAC_MOUNI_ferdu
9COMPT_RTC_CARLO_deuse
10COMPT_RTC_CARLO_juede

 ==>

N° TicketObjet du ticket tronquéNombre de tickets
1;7;8TELEP_TAC_MOUNI3
2;6COMPT_RTC_POULA2
9;10COMPT_RTC_CARLO2
3COMPT_RTC_BIDOU1
4ROR_PDH_CAILLU1
5ROR_PDH_BIDOU1

 

Thank you

4 Replies
Master II
Master II

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é])

Best Regards,
KC
Contributor
Contributor

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.

Master II
Master II

The solution will work for n no of rows.

Best Regards,
KC
Contributor
Contributor

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