Solved: Re: Count where specific text is found in multiple... - Page 2 - Qlik Community

Kev_Thomas · ‎2020-06-06

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 😞

Saravanan_Desingh · ‎2020-06-08

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;

Saravanan_Desingh · ‎2020-06-08

Kev_Thomas · ‎2020-06-08

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

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([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

Kev_Thomas · ‎2020-06-08

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.

Saravanan_Desingh · ‎2020-06-08

Yes, Only header and value are allowed.

Saravanan_Desingh · ‎2020-06-08

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?

Kev_Thomas · ‎2020-06-09

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.

Kev_Thomas · ‎2020-06-12

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

Kev_Thomas · ‎2020-06-13

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.

marcus_sommer · ‎2020-06-14

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

Count where specific text is found in multiple fields

QLIKVIEW Chart

QLIKVIEW Dimensions