topic Re: Sum value Exception in QlikView

Sum value Exception

asknyldz — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

Here is the dashboard.

I want to if Account_Status__c = Lower, it should be not sum in chart.

By the way, why i can't see Account_Status__c values in my chart.

Regards,

Re: Sum value Exception

Anil_Babu_Samineni — Fri, 18 Aug 2017 07:27:39 GMT

Because, you don't have data for that. And i did suppress from presentation

Re: Sum value Exception

stabben23 — Fri, 18 Aug 2017 07:34:06 GMT

Hi,

you should not use concatenate here, romove it. Qlikview will create a "qlikiviewassociation" for you.

Re: Sum value Exception

PrashantSangle — Fri, 18 Aug 2017 07:37:50 GMT

Hi,

in your script you are concatenating two table. because of that you are not getting values against Account_status__c

Instead of concatenate you have to use join (either left or right or outer).

Regards,

Prashant Sangle

Re: Sum value Exception

asknyldz — Fri, 18 Aug 2017 07:54:37 GMT

Thanks Prashant,

How can i join these tables for Id, could you please advice me.

Load * inline

[

Id, Amount,

1,100

2,150

3,200

]

;

Load * inline

[

Id, Account_Status__c,

1,Lower

2,Middle

3,Power

]

;

Re: Sum value Exception

its_anandrjs — Fri, 18 Aug 2017 07:57:48 GMT

Try this ways

Tab1:

Load * inline

[

Id, Amount

1,100

2,150

3,200

];

Left Join(Tab1)

Load * inline

[

Id, Account_Status__c

1,Lower

2,Middle

3,Power

];

You get

Id, Amount,Account_Status__c

1,100,Lower

2,150,Middle

3,200,Power

Regards

Anand

Re: Sum value Exception

PrashantSangle — Fri, 18 Aug 2017 07:58:54 GMT

Hi,

Load * inline

[

Id, Amount,

1,100

2,150

3,200

]

;

join

Load * inline

[

Id, Account_Status__c,

1,Lower

2,Middle

3,Power

]

;

For more details search in help menu.

Regards

Re: Sum value Exception

sasiparupudi1 — Sun, 20 Aug 2017 18:02:22 GMT

Please use the following expression for sum

sum({<Account_Status__c-={'lower'}>}Amount)

in the script

Change it to following

Table1:

Load * inline

[

Id, Amount,

1,100

2,150

3,200

]

;

Left Join(Table1)

Load * inline

[

Id, Account_Status__c,

1,Lower

2,Middle

3,Power

]

;

Re: Sum value Exception

asknyldz — Tue, 22 Aug 2017 09:45:49 GMT

Thanks Parupudi,

I have below "Table not found" error in my script. What do you think, where is my error?

"Table not found

Left Join(Table1)

Load

Id,

Account_Status__c

RESIDENT Account_SQL

WHERE Account_Status__c = 'Rejected'"

CUSTOM CONNECT TO "QvSalesforceConnector";

Opportunity_SQL:

SQL

SELECT

Id,

Amount

FROM

Opportunity

WHERE

FiscalYear = 2017

;

Account_SQL:

SQL

SELECT

Id,

Account_Status__c

FROM

Account

;

Table1:

Load

Id,

Num#(Amount,'0.0') as Amount

Resident Opportunity_SQL

;

Left Join(Table1)

Load

Id,

Account_Status__c

Resident Account_SQL

WHERE Account_Status__c = 'Rejected'

;

Re: Sum value Exception

PrashantSangle — Tue, 22 Aug 2017 10:00:01 GMT

write noconcatenate before Table1:

and run. Also check & confirm that can u see Table1 in Table Viewer

Regards,

Re: Sum value Exception

sasiparupudi1 — Tue, 22 Aug 2017 10:02:32 GMT

Change

Table1:

Load

Id,

Num#(Amount,'0.0') as Amount

Resident Opportunity_SQL

Table1:

Noconcatenate Load

Id,

Num#(Amount,'0.0') as Amount

Resident Opportunity_SQL

Re: Sum value Exception

asknyldz — Tue, 22 Aug 2017 10:13:25 GMT

Dear Prashant,

Here is the dashboard. I need to below result in my chart table, why i can't success.

Account_Status__c	SUM(Amount)
Active	100
Pending Data Validation	150
Pending Sales Leader Approval	125
Rejected	175
Unassigned	75
Unsubmitted	50
Total	675

Re: Sum value Exception

asknyldz — Tue, 22 Aug 2017 10:26:40 GMT

Dear Parupudi,

Here is the dashboard. I need to below result in my chart table, why i can't success.

Account_Status__c	SUM(Amount)
Active	100
Pending Data Validation	150
Pending Sales Leader Approval	125
Rejected	175
Unassigned	75
Unsubmitted	50
Total	675

Re: Sum value Exception

Kushal_Chawda — Tue, 22 Aug 2017 10:39:16 GMT

try below script

CUSTOM CONNECT TO "Provider=QvSalesforceConnector.exe";

Opportunity_SQL:

Load

Id,

Num#(Amount,'0.0') as Amount;

SQL

SELECT

Id,

Amount

FROM

Opportunity

WHERE

FiscalYear = 2017

;

Left Join(Opportunity_SQL)

LOAD Id,

Account_Status__c;

SQL

SELECT

Id,

Account_Status__c

FROM

Account

;

Re: Sum value Exception

asknyldz — Tue, 22 Aug 2017 10:51:18 GMT

Thanks Kushal,

I tried but unfortunately.

Attached is the results.

Re: Sum value Exception

Kushal_Chawda — Tue, 22 Aug 2017 11:02:53 GMT

Please check that the Id values from both the tables are matching or not. Seems it's not matching there for it's not joining the table correctly. Meaning that Id in opportunity table where you have amount is having blank values in Account table

Re: Sum value Exception

asknyldz — Tue, 22 Aug 2017 11:41:41 GMT

Yes my mistake, it was Company_Number__c, not ID