Solved: Re: Formula - Qlik Community

Report Inappropriate Content · ‎2016-03-21

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,

swuehl · ‎2016-03-21

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,', ')

View solution in original post

sunny_talwar · ‎2016-03-21

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

Report Inappropriate Content · ‎2016-03-21

Sorry Sunny here you are.

sunny_talwar · ‎2016-03-21

That was fairly quick. Thank you

sunny_talwar · ‎2016-03-21

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?

Report Inappropriate Content · ‎2016-03-21

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.

swuehl · ‎2016-03-21

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.

swuehl · ‎2016-03-21

Try a list box with a field expression like

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

Report Inappropriate Content · ‎2016-03-21

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

swuehl · ‎2016-03-21

Which field is describing your Company ID?

netsuitecompanyid seems to be completely empty.