Count where specific text is found in multiple fields

[Kev_Thomas]

Hi there,

I have a spreadsheet with a mass of data and trying to avoid doing too much pre-processing in Excel before loading into Qlikview. The data is an extract from Jira and contains a number of fields, but the one's I'm interested in are:

Issue ID - which is the unique identifier of the Jira ticket
TPR - Target Production Release (an identifier that can group records together by a specific TPR)

Labels
Labels1
Labels2
Labels3 etc. upto 
Labels20

What I need to do is count the number of unique Issue IDs where the Labels(n) fields contain data that looks like 'RC*' (using a Wildcard search) and group this by the TPR (which is the dimension on the chart).

Not sure how to construct this in a way that will allow me to plot the number of records (count of Issue ID), by TPR.

Any clues, suggestions would be much appreciated.

Cheers

Kev

I've tried using OR statements in expressions, but can't seem to get a simple line chart going 😞

[rubenmarin]

Hi, if the dimension is TPR the group by would be done by the chart itselft.

And the expresion to count issues can be: Count(Distinct {<[Issue ID]={"RC*"}>} [Issue ID])

[Kev_Thomas]

Thanks for the reply @rubenmarin . There is no mention of the Labels fields though in your statement, but i will give this a try

[marcus_sommer · Accepted solution]

I suggest to change the datamodel to transform your crosstable into a "normal" table. See here what's meant:

The-Crosstable-Load 

- Marcus

[Kev_Thomas]

Thanks @marcus_sommer , that does look useful, but I have a data file with 84 columns and so unless I can split the load of the file into two sections, I may struggle with this approach. Apologies for trying to simplify the question, but it's more that I'm probably not explaining the scenario properly.

A cutdown example of what I'm looking at is show below.

So I need to be able to count any issue id where 'RC*' can be found in any of the Labels Columns.

Issue id	Labels	Labels1	Labels2	Labels3	Labels4	Labels5	Labels6
319626	aaaaaa	bbbbb	RCxxx
322523	cccccc	RCxxx
326580	RCxxx
326988	ddddd	eeeee	fffff	RCxxx
327181	RCxxx
327694	ggggg	RCxxx
331115	hhhhh	iiiii	jjjjj	kkkkk	lllll	RCxxx
331761	mmmmm	nnnnn	ooooo	ppppp	RCxxx
333645	RCxxx

[marcus_sommer]

If you apply the crosstable-load you will get a table with 3 columns: Issue Id, a category-field (name it like you want - it will contain the information which label it is) and a value-field (again you could name it and it contained the values).

After it you could load this table again and adding:

if(value like 'RC*', 1, 0) as Flag

and then you could just use:

sum(Flag)

within any UI object or you may use this flag-field within a set analysis or in a listbox - it's quite simple.

- Marcus

[Kev_Thomas]

@marcus_sommer - I see what you're getting at now. Thanks for the advice. I'll certainly gen up on the crosstable load and see how I can apply this.

I (foolishly?) was under the impression you could only do one load from a file per "project".

I'll let you know how it goes. Thanks again. 🙂

[rubenmarin]

Sorry, a mix of my bad english and fast reading and I skip an important part, it looks clearer with the sample data.

I think Marcus solution is the best approach.

An OR in set analysis can be done as: =Count(Distinct {<Label1={"RC*"}>+<Label2={"RC*"}>+<Label3={"RC*"}>...to Label20} [Issue ID])

[Saravanan_Desingh]

One solution is.

tab1:
LOAD * INLINE [
    Issue id, Labels, Labels1, Labels2, Labels3, Labels4, Labels5, Labels6
    319626, aaaaaa, bbbbb, RCxxx,  ,  ,  ,  
    322523, cccccc, RCxxx,  ,  ,  ,  ,  
    326580, RCxxx,  ,  ,  ,  ,  ,  
    326988, ddddd, eeeee, fffff, RCxxx,  ,  ,  
    327181, RCxxx,  ,  ,  ,  ,  ,  
    327694, ggggg, RCxxx,  ,  ,  ,  ,  
    331115, hhhhh, iiiii, jjjjj, kkkkk, lllll, RCxxx,  
    331761, mmmmm, nnnnn, ooooo, ppppp, RCxxx,  ,  
    333645, RCxxx,  ,  ,  ,  ,  ,  
];

tab1X:
CrossTable(Key, Value)
LOAD * Resident tab1;

tab2:
NoConcatenate
LOAD *
Resident tab1X
Where Value Like 'RC*'
;

Drop Table tab1, tab1X;

[Saravanan_Desingh]