topic Re: Simple load and group script in QlikView

Simple load and group script

Anonymous — Mon, 04 May 2015 15:27:12 GMT

Hi,

I have the following script which gives me a script error:

LOAD [NonSupplyID] AS [CaseRecordID]

      ,[BillRuleID] AS [BillingRuleID]

      ,[BillRuleID]

      ,[PriceAMT]

      ,if(BillRuleID=48 OR BillRuleID=55 OR BillRuleID=93,[PriceAMT],0) AS NonSupplyPriceA

      ,if(BillRuleID<>48 and BillRuleID<>55 and BillRuleID<>93,[PriceAMT],0) AS NonSupplyPriceS

      ;

SQL SELECT *

   FROM [PICIS].[Finance].[CaseRecordNonSupply];



CaseRecordNonSupply:

LOAD CaseRecordID, BillingRuleID,BillRuleID,PriceAMT

,Sum(NonSupplyPriceA) AS NonSupplyPriceAnesthetist

,Sum(NonSupplyPriceS) as NonSupplyPriceSurgeon

Resident CaseRecordNonSupply_temp

GROUP BY CaseRecordID;

DROP Table CaseRecordNonSupply_temp;

Re: Simple load and group script

Gysbert_Wassenaar — Mon, 04 May 2015 16:16:02 GMT

You need to add all the fields that are not used in aggregation functions to the GROUP BY clause:

CaseRecordNonSupply:
LOAD CaseRecordID, BillingRuleID,BillRuleID,PriceAMT
,Sum(NonSupplyPriceA) AS NonSupplyPriceAnesthetist
,Sum(NonSupplyPriceS) as NonSupplyPriceSurgeon
GROUP BY CaseRecordID, BillingRuleID,BillRuleID,PriceAMT;

Re: Simple load and group script

Anonymous — Mon, 04 May 2015 16:52:02 GMT

Hi Gysbert,

Thanks very much!!

How stupid of me…

Jonathan