Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 😞
I suggest to change the datamodel to transform your crosstable into a "normal" table. See here what's meant:
- Marcus
Hi @Brett_Bleess - I've had so much help from people on this thread, but I think @marcus_sommer probably deserves the accolade as he provided most guidance. However kudos to @Saravanan_Desingh for his suggestions on the Crosstable solution.
I have resorted to refactoring the input data to include flags instead of trying to do the calculations within Qlikview itself, but without the suggestions and support from these guys - I would never have got there.
Thanks all
Kev
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])
Thanks for the reply @rubenmarin . There is no mention of the Labels fields though in your statement, but i will give this a try
I suggest to change the datamodel to transform your crosstable into a "normal" table. See here what's meant:
- Marcus
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 |
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
@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. 🙂
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])
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;