topic Formula in QlikView

Formula

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I need your assistance with two issues. Number one I keep getting Out of Object Memory error for my chart. And Number two is there any formula to see if any company id has more than one phone number. I'm attaching a sample data set.

Thanks in advance,

Re: Formula

sunny_talwar — Mon, 21 Mar 2016 20:44:43 GMT

Wow, isn't it possible to provide a reduced version of the actual application?

Re: Formula

Mon, 21 Mar 2016 20:47:32 GMT

Sorry Sunny here you are.

Re: Formula

sunny_talwar — Mon, 21 Mar 2016 20:48:26 GMT

That was fairly quick. Thank you

Re: Formula

sunny_talwar — Mon, 21 Mar 2016 20:50:53 GMT

Where is the chart that is showing 'Out of Memory'? I see only list boxes

Customer has multiple phones? which phone? 1, 2, 3 or 4 or the among the combination?

Re: Formula

Mon, 21 Mar 2016 20:54:41 GMT

I got the Out of Memory matter resolved. Yes Customer has multiple phone numbers but ignore the 2,3,4 phone1 is the field which is needed. I want to see which customers have multiple phone numbers please.

Thanks for checking.

Re: Formula

swuehl — Mon, 21 Mar 2016 20:54:43 GMT

If a chart returns out of object memory, I assume that's because your expression uses fields from both your unlinked tables, hence Qlik creates a temporary cartesian product to calculate the aggregation results.

This probably causes your issues.

Re: Formula

swuehl — Mon, 21 Mar 2016 20:56:12 GMT

Try a list box with a field expression like

Aggr( Only({<customer = {"=Count(DISTINCT phone1)>1"}>} customer), customer)

Re: Formula

Mon, 21 Mar 2016 21:02:23 GMT

Thanks Stefan does this formula provide me with the Company IDs which have multiple phone numbers?

Re: Formula

swuehl — Mon, 21 Mar 2016 21:14:04 GMT

Which field is describing your Company ID?

netsuitecompanyid seems to be completely empty.

Re: Formula

Mon, 21 Mar 2016 21:19:46 GMT

NetsuiteID. I want to make a chart with IDs and ALL the phone1 numbers associated with them. And a dimension or a expression which entails how many phone1 each ID has.

Thanks,

Re: Formula

swuehl — Mon, 21 Mar 2016 21:35:57 GMT

This calculated dimension (e.g. used in a list box's field expression) should return the list of companies with more than one phone1 entries:

=Aggr(

Only({1<Customer2.netsuiteid = {"=Count({1}DISTINCT Customer2.phone1)>1"} >}Customer2.netsuiteid), Customer2.netsuiteid)

You can also create a chart with dimension Customer2.netsuiteid and expression

=Count(DISTINCT Customer2.phone1)>1

which should show the same set of companies. Add phone1 as dimension if you want (or an expression like

=Concat(

{<Customer2.netsuiteid = {"=Count({1}DISTINCT Customer2.phone1)>1"} >}

DISTINCT Customer2.phone1,', ')

Re: Formula

Mon, 21 Mar 2016 22:07:27 GMT

Thanks Stefan. I have created a chart with ID and the phone1 field but some odd reason the chart only displays only one phone number even though there are more than one phone number associated with some of them. The first one which jumps out at me is ID 460 it has two numbers and the chart doesn't even list them.

Please provide assistance in this matter .

Re: Formula

swuehl — Mon, 21 Mar 2016 22:21:22 GMT

I can't see that you have used any of my suggested calculated dimensions / expression in your sample app.

I think the reason why you see only single phone1 numbers and no duplicates is

Use Aggregation Functions!

Re: Formula

Tue, 22 Mar 2016 12:58:12 GMT

I'm keeping the chart as simple as possible in my sample so I can see the multiple phone numbers (if any) for the customers. It's a requirement for the end users. I'm also using one of the formulas you had suggested above to see which IDs have multiple phones listed.

=Aggr(Only({1<Customer2.netsuiteid = {"=Count({1}DISTINCT Customer2.phone1)>1"} >}Customer2.netsuiteid), Customer2.netsuiteid)

So if I understood the discussion by Henric Aggr function eliminates the dupes, right?

Please I just need the IDs to show other phone numbers too not just the first matching phone number.

Thanks,

Arsalan

Re: Formula

Tue, 22 Mar 2016 13:31:34 GMT

Got it!!!! Thanks for all of your help guys, Sunny and Stefan.