topic Re: Count where specific text is found in multiple fields in QlikView

Count where specific text is found in multiple fields

Kev_Thomas — Sat, 16 Nov 2024 00:31:49 GMT

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 😞

Re: Count where specific text is found in multiple fields

rubenmarin — Sun, 07 Jun 2020 09:13:35 GMT

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

Re: Count where specific text is found in multiple fields

Kev_Thomas — Sun, 07 Jun 2020 09:35:34 GMT

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

Re: Count where specific text is found in multiple fields

marcus_sommer — Sun, 07 Jun 2020 11:00:42 GMT

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

The-Crosstable-Load

- Marcus

Re: Count where specific text is found in multiple fields

Kev_Thomas — Sun, 07 Jun 2020 12:11:53 GMT

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

Re: Count where specific text is found in multiple fields

marcus_sommer — Sun, 07 Jun 2020 13:13:00 GMT

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

Re: Count where specific text is found in multiple fields

Kev_Thomas — Sun, 07 Jun 2020 15:42:12 GMT

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

Re: Count where specific text is found in multiple fields

rubenmarin — Sun, 07 Jun 2020 16:15:53 GMT

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

Re: Count where specific text is found in multiple fields

Saravanan_Desingh — Sun, 07 Jun 2020 21:28:13 GMT

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;

Re: Count where specific text is found in multiple fields

Saravanan_Desingh — Sun, 07 Jun 2020 21:29:00 GMT

Re: Count where specific text is found in multiple fields

Saravanan_Desingh — Mon, 08 Jun 2020 14:13:40 GMT

I completely missed the Count part.

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: LOAD [Issue id], Count(Value) As Cnt Resident tab1X Where Value Like 'RC*' Group By [Issue id] ; Drop Table tab1, tab1X;

Re: Count where specific text is found in multiple fields

Saravanan_Desingh — Mon, 08 Jun 2020 14:14:56 GMT

Re: Count where specific text is found in multiple fields

Kev_Thomas — Mon, 08 Jun 2020 18:44:51 GMT

@Saravanan_Desingh - thanks for your input.

I'm actually just getting a syntax error while trying to follow your idea. Not sure where I'm going wrong with this. Sorry if it's a lot simpler than I'm grasping.

This is my load script.

tabRC:
CrossTable([Issue id],Labels,Labels1,Labels2,Labels3,Labels4)
LOAD * Resident AllData;

Syntax error is:

Unknown statement: tabRC:
CrossTable([Issue id],Labels,Labels1,Labels2,Labels3,Labels4)
LOAD * Resident AllData

tabRC:
CrossTable([Issue id],Labels,Labels1,Labels2,Labels3,Labels4)
LOAD * Resident AllData

Re: Count where specific text is found in multiple fields

Kev_Thomas — Mon, 08 Jun 2020 19:31:05 GMT

So it looks like you can only have (key, value) on the CrossTable statement....fair enough. 🙂 Think I can see what has to be done.

Re: Count where specific text is found in multiple fields

Saravanan_Desingh — Mon, 08 Jun 2020 19:53:04 GMT

Yes, Only header and value are allowed.

Re: Count where specific text is found in multiple fields

Saravanan_Desingh — Mon, 08 Jun 2020 19:57:21 GMT

AllData: LOAD RCCount, RICount, RRCount, [Issue Type], [Issue key], [Issue id], Summary, Status, Severity, [Test Env], TPR, Proposition, Proposition1, Proposition2, Proposition3, Proposition4, Proposition5, [Feature Team], Labels, Labels1, Labels2, Labels3, Labels4, Labels5, Labels6, Labels7, Labels8, Labels9, Labels10, Labels11, Labels12, Labels13, Labels14, Labels15, Labels16, Labels17, Labels18, Labels19, Labels20, Labels21, Labels22, Labels23, Labels24, Labels25, Labels26, Labels27, Labels28, Labels29, Labels30, Labels31, Labels32, Labels33, Labels34, Labels35, Labels36, Labels37, Labels38, Labels39, Labels40, Labels41, Labels42, Labels43, Labels44, Labels45, Labels46, Labels47, Labels48, Labels49, Labels50, Labels51, Labels52, Labels53, Labels54, Labels55, Labels56, Labels57, Labels58, Labels59, Labels60, Labels61, Labels62, Labels63, Labels64, Labels65 FROM [C:\Users\TapTap\Documents\Qlikview\iBStats.xlsx] (ooxml, embedded labels); tabRC: CrossTable(Key, Value, 18) LOAD RCCount, RICount, RRCount, [Issue Type], [Issue key], [Issue id], Summary, Status, Severity, [Test Env], TPR, Proposition, Proposition1, Proposition2, Proposition3, Proposition4, Proposition5, [Feature Team], Labels, Labels1, Labels2, Labels3, Labels4, Labels5, Labels6, Labels7, Labels8, Labels9, Labels10, Labels11, Labels12, Labels13, Labels14, Labels15, Labels16, Labels17, Labels18, Labels19, Labels20, Labels21, Labels22, Labels23, Labels24, Labels25, Labels26, Labels27, Labels28, Labels29, Labels30, Labels31, Labels32, Labels33, Labels34, Labels35, Labels36, Labels37, Labels38, Labels39, Labels40, Labels41, Labels42, Labels43, Labels44, Labels45, Labels46, Labels47, Labels48, Labels49, Labels50, Labels51, Labels52, Labels53, Labels54, Labels55, Labels56, Labels57, Labels58, Labels59, Labels60, Labels61, Labels62, Labels63, Labels64, Labels65 Resident AllData;

Can u try this?

Re: Count where specific text is found in multiple fields

Kev_Thomas — Tue, 09 Jun 2020 18:25:41 GMT

Thanks again @Saravanan_Desingh for your input.

I'm still struggling with the basics of getting a simple plot of TPR vs Count. Data seems to be loading in ok, more than likely just my lack of knowledge letting me down.

Re: Count where specific text is found in multiple fields

Kev_Thomas — Fri, 12 Jun 2020 14:48:49 GMT

@Saravanan_Desingh - following your guidance, I'm definitely getting there.

I'm using this as my load script at present (I know I'm missing the table drops). This allows me to create the table with fields which match the RC* that I'm looking for.

AllData:
LOAD
[Issue Type],
[Issue key],
[Issue id],
Summary,
Status,
Severity,
[Test Env],
TPR,
Proposition,
Proposition1,
Proposition2,
Proposition3,
Proposition4,
Proposition5,
[Feature Team],
Labels,
Labels1,
Labels2,
Labels3,
Labels4,
Labels5,
Labels6,
Labels7,
Labels8,
Labels9,
Labels10,
Labels11,
Labels12,
Labels13,
Labels14,
Labels15,
Labels16,
Labels17,
Labels18,
Labels19,
Labels20,
Labels21,
Labels22,
Labels23,
Labels24,
Labels25,
Labels26,
Labels27,
Labels28,
Labels29,
Labels30,
Labels31,
Labels32,
Labels33,
Labels34,
Labels35,
Labels36,
Labels37,
Labels38,
Labels39,
Labels40,
Labels41,
Labels42,
Labels43,
Labels44,
Labels45,
Labels46,
Labels47,
Labels48,
Labels49,
Labels50,
Labels51,
Labels52,
Labels53,
Labels54,
Labels55,
Labels56,
Labels57,
Labels58,
Labels59,
Labels60,
Labels61,
Labels62,
Labels63,
Labels64,
Labels65
FROM
[C:\Users\TapTap\Documents\Qlikview\iBStats.xlsx]
(ooxml, embedded labels);

cntRC:
Load TPR, Count(DISTINCT([Issue id]))
Resident AllData
where Labels like 'RC*'
or Labels1 like 'RC*'
or Labels2 like 'RC*'
or Labels3 like 'RC*'
or Labels4 like 'RC*'
or Labels5 like 'RC*'
or Labels6 like 'RC*'
or Labels7 like 'RC*'
or Labels8 like 'RC*'
or Labels9 like 'RC*'
or Labels10 like 'RC*'
or Labels11 like 'RC*'
or Labels12 like 'RC*'
or Labels13 like 'RC*'
or Labels14 like 'RC*'
or Labels15 like 'RC*'
or Labels16 like 'RC*'
or Labels17 like 'RC*'
or Labels18 like 'RC*'
or Labels19 like 'RC*'
or Labels20 like 'RC*'
or Labels21 like 'RC*'
or Labels22 like 'RC*'
or Labels23 like 'RC*'
or Labels24 like 'RC*'
or Labels25 like 'RC*'
or Labels26 like 'RC*'
or Labels27 like 'RC*'
or Labels28 like 'RC*'
or Labels29 like 'RC*'
or Labels30 like 'RC*'
or Labels31 like 'RC*'
or Labels32 like 'RC*'
or Labels33 like 'RC*'
or Labels34 like 'RC*'
or Labels35 like 'RC*'
or Labels36 like 'RC*'
or Labels37 like 'RC*'
or Labels38 like 'RC*'
or Labels39 like 'RC*'
or Labels40 like 'RC*'
or Labels41 like 'RC*'
or Labels42 like 'RC*'
or Labels43 like 'RC*'
or Labels44 like 'RC*'
or Labels45 like 'RC*'
or Labels46 like 'RC*'
or Labels47 like 'RC*'
or Labels48 like 'RC*'
or Labels49 like 'RC*'
or Labels50 like 'RC*'
or Labels51 like 'RC*'
or Labels52 like 'RC*'
or Labels53 like 'RC*'
or Labels54 like 'RC*'
or Labels55 like 'RC*'
or Labels56 like 'RC*'
or Labels57 like 'RC*'
or Labels58 like 'RC*'
or Labels59 like 'RC*'
or Labels60 like 'RC*'
or Labels61 like 'RC*'
or Labels62 like 'RC*'
or Labels63 like 'RC*'
or Labels64 like 'RC*'
or Labels65 like 'RC*'
Group By TPR;

Based on having two Target Production Releases (iB18c and iB19) in my data set, I'm pulling back two counts which are very close to what I expect.

However, I know that the value of 200 should actually be a value of 195 and I know where the +5 records are being picked up from, but I need to try and filter those out from my final count.

In order to filter the data more accurately, I need to be able to do the following:

if(left(labels,6)=concat('RC',left(TPR4), <count this record>

I'm just not sure whether this should be added as an expression to my chart (which I'm now plotting the TPR and Count against).

For reference.
TPR = 'iB19 - 2020'
Labels = 'RCiB19.1'

So I'm looking to plot only the values where TPR = 'RC' + the first 4 characters or TPR i.e. iB19 = RCiB19.

Again, any thoughts or suggestions would be greatly appreciated. I'm close, but frustratingly still far away from what I'm hoping to achieve.

Re: Count where specific text is found in multiple fields

Kev_Thomas — Sat, 13 Jun 2020 21:22:31 GMT

So, the saga continues.

I've carried out a crosstable to give me every TPR (iB18c - 2020, iB19 - 2020) and all Labels1-65 have been consolidated in the Column called Value.

When trying to plot a chart - I'm using the expression detailed below:

Value would = something like 'RCiB19.6.1'
TPR would = 'iB18c - 2020' or 'iB19 - 2020'

Therefore, I'm looking to plot a count where:

Value = LEFT(Value,6) = 'RCiB19'
Concat= 'RC' & LEFT(TPR,4) = 'RC' & 'iB19' = 'RCiB19'

=if(LEFT(Value,6)=Concat('RC',Left(TPR,4)),Count([Issue id]),0)

However, this doesn't appear to work. 😞

Again, sorry for the constant questions around this, and thank you so much to those of you that have kindly made suggestions.

Re: Count where specific text is found in multiple fields

marcus_sommer — Sun, 14 Jun 2020 06:45:54 GMT

Concat() is in Qlik a string-aggregation over columns like sum() or max() and not a concatenation on the row-level (Qlik has no own function for it - the wanted parts are just added per "&" like: Field1 & '|' & Field2). Therefore your approach couldn't work.

I'm not quite sure what do you want to do - some kind of grouping the data ? - but I suggest to prepare / pre-calculate most things within the script-level and if possible without the us of if-loops. This means to use subfield() to split the string and/or keepchar/purgechar() to extract/remove chars, for example numbers. Another approach would be not to do this grouping with various measures directly within the loads else to apply a mapping or even to use a linked dimension-table.

- Marcus