Sum of a field in the script

[ranibosch]

good day,

can someone please tell me what is wrong with this script?

[Anonymous]

What's the error you're getting?

[Anonymous]

I'm not very good at Qlikview scripting... but I know SQL well. I see that you have used Group by as follows:

GeneralLedgerEntries:

Load

Year,

Month,

Department,

BU,

"Document No",

Sum (Amount) as "Sales Value"

Resident TempGeneralLedgerEntries

Group by "Document No";

--------------------------------------------

In the above, you might be dropping out records. You should group by all the columns that you've selected. i.e:

GeneralLedgerEntries:

Load

Year,

Month,

Department,

BU,

"Document No",

Sum (Amount) as "Sales Value"

Resident TempGeneralLedgerEntries

Group by

Year,

Month,

Department,

BU,

"Document No";

----------------------------------------------

Is missing records the problem you're trying to address?

[raman_rastogi]

Rani

*  Where "G_L Account No_" in (1110,1120,1130,1140,1150)   Use  Match Function

Ex- where Match("G_L Account No_" ,1110,1120,1130,1140,1150 )

*  GeneralLedgerEntries:

Load

Year,

Month,

Department,

BU,

"Document No",

Sum (Amount) as "Sales Value"

Resident TempGeneralLedgerEntries

Group by

Year,

Month,

Department,

BU,

"Document No";

Here you have to add remaining fields in group by.

Or you can Just use Sum(Amount) as a expression in qlikview charts you will find same which you try to get.

Regards

Raman

[jasonmomoa]

Hi ranibosch‌,

As commented above, when using Group By clause, you'll need to specify all dimensions you want to group by (in your example you're missing some). Otherwise, you'll be missing records.

Regards,