Group by clause with Flags in Qliksense Script

[SunainaUmberkar]

Hello All,

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
;

Kindly help. Thanks in advance.

Regards,

Sunaina

[henrikalmen]

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.

[marcus_sommer]

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
;

[SunainaUmberkar]

Hello Marcus,

Thanks for the quick response. I will check on this approach.

[SunainaUmberkar]

Hello henrikalmen,

Sorry my bad. Its typo mistake.