topic Re: Custom field based on a count condition on another field in QlikView

Custom field based on a count condition on another field

RCDP — Thu, 03 Oct 2019 09:43:30 GMT

Hello,

I'm new to Qlik and I've implemented a report based on call registry of a call center.

Qhat I'm trying to do is to generate a custom field (boolean Yes or No) based on a count condition.

If I count for the same call ID (uniqueID) more than one record and at least one of them have no operator information (detailOperatorExten) I have to mark che custom field "Owerflow" with Yes, else if all record have no operator information No.

Could you please help me?

Thanks

Riccardo

Re: Custom field based on a count condition on another field

Arthur_Fong — Thu, 03 Oct 2019 10:01:11 GMT

What if all records has operation info? Display yes?

Re: Custom field based on a count condition on another field

RCDP — Thu, 03 Oct 2019 10:07:47 GMT

uniqueID	status	detailoperationexten	Overflow
1	served	101	No
2	served	-	Yes
2	served	201	No
3	not served	-	Yes
3	not served	-	No
4	not served	-	No

uniqueID, status and detailoperationexten all come from a CSV file. The "Oweflow" is a custom field that need to tell me if a call was passed to another queue when the first queue did not answer in time.

So in this scenario the call 2 and 3 are that on which I have to mark this info.

Hope to be clear enough.

Thanks

Re: Custom field based on a count condition on another field

Brett_Bleess — Thu, 10 Oct 2019 13:16:58 GMT

Riccardo, I am going to give you a URL link to the Design Blog area of the Community, you will find a bunch of examples of how to do different things there, not sure there is anything specific related to your current scenario, but you may find some things that will give you some other ideas as well.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

With my post, things will be kicked back up in the list again, so someone else may shout back again given you posted the extra info... Just wanted to try to give you something in the meantime, sorry I am not more help to you.

Regards,
Brett

Re: Custom field based on a count condition on another field

StarinieriG — Thu, 10 Oct 2019 13:47:59 GMT

Hi,

maybe you could try something like this

TABLE:
LOAD * Inline [
uniqueID,status,detailoperationexten
1,served,101,
2,served,
2,served,201
3,not served,
3,not served,
4,not served,
];

Left Join (TABLE)
LOAD
uniqueID,
Count(uniqueID) as CountID
Resident TABLE
Group By
uniqueID
;

Left Join (TABLE)
LOAD
uniqueID,
Count(detailoperationexten) as Countdetail
Resident TABLE
Where Len(Trim(detailoperationexten))=0
Group By
uniqueID
;

NoConcatenate
OUTPUT:
LOAD
uniqueID,
status,
detailoperationexten,
If(CountID>1 and Countdetail<>0,'Yes','No') as Overflow
Resident TABLE;

DROP Table TABLE;

Re: Custom field based on a count condition on another field

RCDP — Mon, 14 Oct 2019 08:42:03 GMT

Thanks, it works!