I need to develop a logic in Qliksense Script with group by clause.
There are multiple calculations to be done. In the Table3, I need to do a calculation and also create a flag.
Below is the code. I am getting Invalid Expression for table3 for this calculation Sum(Interest)/nAvgBal/Days1.
I tried this code in preceeding load as well but error is same.
MainTable: Select ID,DayofMonth,Balance,Interest from qvd;
Table1: Select ID, DayofMonth, Sum(Balance) as SumBal, Sum(Interest) as Interest resident MainTable group by ID, DayofMonth;
Table2: Load ID, Avg(SumBal) as [Avg Balance], Sum(Interest) as Interest, Count(SumBal) AS Days, Count(SumBal)*365 AS Days1, If(Avg(SumBal)=0,0,Avg(SumBal)) as AvgBal Resident Table1 Group by ID ;
Table3: Select ID, Avg(SumBal) as AvgBal, Sum(Interest) as Interest1, Count(SumBal) as Days1, Sum(Interest)/nAvgBal/Days1 as InterestRate Resident Table2 Group by ID ;
Table3 is loading data from Table2, but in Table2 there is no field named SumBal and no field named nAvgBal (although there is a field named AvgBal). So it seems you are trying to do calculations with data that doesn't exists in Table2 when creating Table3.
You want to access fields which are created in this load - that's not valide because only fields which exists already in the source could be fetched. You may change your statement to:
Table3: load ID, Avg(SumBal) as AvgBal, Sum(Interest) as Interest1, Count(SumBal) as Days1, Sum(Interest) / Avg(SumBal) / Count(SumBal) as InterestRate Resident Table2 Group by ID ;
