Min of 2 Dimension!!

[#1]

Hi guys I have the following tabel,

Ticket	Changes	End_Severity	Minimum
123	Ticket Open	3	1
123	Changed To Sev 2	3	1
123	Random Change	3	1
123	Changed To Sev 1	3	1
123	Ticket Closed	3	1
456	Ticket Open	3	3
456	Random Change	3	3
456	Random Change	3	3
456	Ticket Closed	3	3

Hi Guys I have the following table and would like to get the results that show on the Minimum Column,  this basically represents a ticketing system, and tickets are given severities, some tickets change severities during changes and some dont, so what I want want to achieve is if there has been no changes in Severity by shown in the "Changes" Column, then I would like to get the result of the "End_Severity" Column, HOWEVER if there has been changes in the Severity looking for the following string "Changed To" in the changes table I want to compare the severity value with the severity value of the "End Severity" Column and I would like to get the lowest Severity Number.

Ideally I think ill need to aggregate it as well as I will need to show this in a line graph, by number of Tickets for each month according to Lowest Severity.

Many thanks

[#2]

Hello,

Please find the attached.

Hope this was helpful

Thanks,

Singh

[maxgro]

- 2 dimension

Ticket, Changes

- expression

rangemin(     min(total <Ticket> if(wildmatch(Changes, 'Changed To*'), right(Changes,2))),

                     min(total <Ticket> End_Severity)

               )

[MarcoWedel · Accepted solution]

Hi,

one possible solution:

tabTickets:
LOAD Ticket,
    Changes,
    End_Severity
FROM [http://community.qlik.com/thread/133021] (html, codepage is 1252, embedded labels, table is @1);

Left Join
LOAD Ticket,
    RangeMin(Min(SubField(Changes, 'Changed To Sev ', 2)), Min(End_Severity)) as Minimum
Resident tabTickets
Group By Ticket;

hope this helps

regards

Marco

[#5]

Hi Massimo Thanks for the Reply your solution works well, however whenever i use your expression as a Calculated dimension I get no values at all, im using this as a calculated iImension along with Month, and then my expression is a Count(Distinct (Ticket)), as I want to see the Count of tickets per month by Severity.

Thanks

[#6]

Hi Marco this works nicely, but I have found out that the End_Severity field also has values that are not numbers, and this will leave those fields blanks for the calculation, is there something I can add, to say if value null use End_Severity?

Thanks

[#7]

Hi Angad, thanks for this, but I am using the Personal Edition, my company will only have Licensed editions by the end of the month.

Thanks for you effort thogh, could you post the script in here?

[#8]

Here it goes:

Script:

test:

LOAD * INLINE [

Ticket,Changes,End_Severity

123,Ticket Open,3

123,Changed To Sev2,3

123,Random Change,3

123,Changed To Sev1,3

123,Ticket Closed,3

456,Ticket Open,3

456,Random Change,3

456,Random Change,3

456,Ticket Closed,3];

test1:

LOAD

Ticket,

sum(SubStringCount(Changes,'Changed')) as ChangedCount

Resident test

group by Ticket;

left join(test)

LOAD

Ticket,

if(ChangedCount>=1,1,0) as ChangedFlag

Resident test1;

drop table test1;

Chart:

Use Expression of Minimum as : if(ChangedFlag=1,1,End_Severity)

Though you have much better ways to do this, as mentioned by my fellow mates

--Singh

[MarcoWedel]

unclear.

Use End_Severity if End_Severity is null?

Please post all special input values and expected result.

thanks

regards

Marco