topic Re: Need Urgent Help...Calculation Between Rows in QlikView

Need Urgent Help...Calculation Between Rows

Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have one table ,

Date CustomerId ErrorCode Status

12/1/2016 A 1 UnResolved

12/2/2016 A 2 UnResolved

12/2/2016 A 3 UnResolved

12/3/2016 B 1 UnResolved

12/3/2016 B 2 UnResolved

12/3/2016 B 3 UnResolved

12/2/2016 A 1 Resolved

12/3/2016 A 2 UnResolved

12/3/2016 A 3 UnResolved

12/3/2016 A 2 Resolved

12/3/2016 A 3 UnResolved

I have few queries

How can calculate number of Unresolved CustomerIds (where Status is Unresolved) ..in above example, Cutomer id A is unresolved because m error Code 3 for Customer id A is still unresolved,

How to create cycle time, for resolved Error, for example Cutomer Id A and error 1 is resolved on 12.2.2016 so my Cycle time is 12/2/2016-12/1/2016+1

How can I calculate Aging for Unresolved cases

Thanks for your help in advance

Regards

Siddharth

Re: Need Urgent Help...Calculation Between Rows

Anil_Babu_Samineni — Wed, 04 Jan 2017 06:29:11 GMT

Try like below and if not, Can you provide expected table to look w.r.to Expecting?

1) Count({<Status = {"UnResolved"}>}Status)

2) If(Status = 'Resolved', Count({<Date = {Interval(Only({<Status = {'Resolved'}>}Date) - Only({<Status = {'UnResolved'}>}Date))}>} Status),Count({<Status = {"UnResolved"}>}Status))

Re: Need Urgent Help...Calculation Between Rows

MK_QSL — Wed, 04 Jan 2017 08:56:22 GMT

Your description is not clear.

Do you want to count number of CustomerID whose status is Unresolved at the max date?

Re: Need Urgent Help...Calculation Between Rows

Wed, 04 Jan 2017 08:59:31 GMT

Thanks Anil for your kind reply.

Unfortunately Same Customer Id and Same Code can have multiple entries as Unresolved over several dates

Please find here with more specific table

Id	Code	FileDate	Status
1	A	12/1/2016	UnResolved
1	B	12/2/2016	UnResolved
2	A	12/2/2016	UnResolved
1	A	12/2/2016	Resolved
1	B	12/3/2016	UnResolved
1	B	12/4/2016	UnResolved
2	A	12/4/2016	UnResolved
2	A	12/5/2016	Resolved
Total No of Id		2	(1 and 2 are distict Ids)
Total Error		3	(1_A,1_B,2_A)
Total Open Errors		1	(1_B is still not Resolved)
Total Open Id		1	Although 1_B and 1_C is closed, but 1_A is still unresolved so overall 1 is still open
Closed Id	1	Id=2 is closed
Cycle Time for error		for 2_A, it is =5-2+1

Regards

Siddharth

Re: Need Urgent Help...Calculation Between Rows

Anil_Babu_Samineni — Wed, 04 Jan 2017 10:36:30 GMT

In your table, You are showing A and 3. But now table was different. Can you clear on this to make understand. What was the expected output

Can you send Excel with the data with Output, Please?

Re: Need Urgent Help...Calculation Between Rows

MK_QSL — Wed, 04 Jan 2017 10:36:45 GMT

Where is 1_C in this example?

Total Open ID = 1... 1_A not resolved? How???

Please provide proper information.

Re: Need Urgent Help...Calculation Between Rows

Wed, 04 Jan 2017 11:55:09 GMT

Hi Anil,

Yes you are right, this one is new table , I have simplified it

Total No of Id		2	(1 and 2 are distict Ids)
Total Error		3	(1_A,1_B,2_A)
Total Open Errors		1	(1_B is still not Resolved)
Total Open Id		1	Although 1_B and 1_C is closed, but 1_A is still unresolved so overall 1 is still open
Closed Id	1	Id=2 is closed
Cycle Time for error		for 2_A, it is =5-2+1

this one is my expected output from below mentioned table

Id	Code	FileDate	Status
1	A	12/1/2016	UnResolved
1	B	12/2/2016	UnResolved
2	A	12/2/2016	UnResolved
1	A	12/2/2016	Resolved
1	B	12/3/2016	UnResolved
1	B	12/4/2016	UnResolved
2	A	12/4/2016	UnResolved
2	A	12/5/2016	Resolved

Regards

Siddharth

Re: Need Urgent Help...Calculation Between Rows

Wed, 04 Jan 2017 11:57:51 GMT

Hi Manish,

Thanks for noticing it, Id 1 has 2 errors, if any one of it is still open then we consider id as open.

Think Id as Client Id, if any of his reported error is still not resolved, we will consider ticket (Customer Level..in this case Id ) as Unresolved

Note: I created new table so that I can show exact requirements

Regards

Siddharth

Re: Need Urgent Help...Calculation Between Rows

MK_QSL — Wed, 04 Jan 2017 12:14:58 GMT

Where is 1_C here?

You are confusing now...!!!

Although 1_B and 1_C is closed

Re: Need Urgent Help...Calculation Between Rows

Wed, 04 Jan 2017 12:28:25 GMT

Hi Manish,

I am sorry,its my fault I changes table and hence no I_C in new table.

Can you please help me with one thing.

Can you help me flag these 2 last columns of these tables,

Id	Code	FileDate	Status	Flag Where FIleDate is Minimum for that Id and Code Combination	Flag where DateFIle is Max for that Id and Code Combination
1	A	12/1/2016	UnResolved	1	0
1	B	12/2/2016	UnResolved	1	0
2	A	12/2/2016	UnResolved	1	0
1	A	12/2/2016	Resolved	0	1
1	B	12/3/2016	UnResolved	0	0
1	B	12/4/2016	UnResolved	0	1
2	A	12/4/2016	UnResolved	0	0
2	A	12/5/2016	Resolved	0	1

Regards

Siddharth

Re: Need Urgent Help...Calculation Between Rows

MK_QSL — Wed, 04 Jan 2017 12:56:18 GMT

Temp:

Load

Id,

Code,

Id & Code as %Key,

Date(Date#(FileDate,'MM/D/YYYY')) as FileDate,

Status

Inline

[

Id, Code, FileDate, Status

1, A, 12/1/2016, UnResolved

1, B, 12/2/2016, UnResolved

2, A, 12/2/2016, UnResolved

1, A, 12/2/2016, Resolved

1, B, 12/3/2016, UnResolved

1, B, 12/4/2016, UnResolved

2, A, 12/4/2016, UnResolved

2, A, 12/5/2016, Resolved

];

Left Join (Temp)

Load %Key, Max(FileDate) as MaxDate, Min(FileDate) as MinDate Resident Temp Group By %Key;

Final:

Load Id, Code, FileDate, If(FileDate=MaxDate,1,0) as MaxFlag, If(FileDate=MinDate,1,0) as MinFlag Resident Temp;

Drop Table Temp;

Re: Need Urgent Help...Calculation Between Rows

Wed, 04 Jan 2017 14:26:42 GMT

Thanks Manish,

You are my hero ,Thanks a Ton !!!

It really helped me solve my problem !!!

Thankyou once again !!!

Regards

Siddharth