count in Script Load

[chandra63]

Hi,

Need urgent help here.

Hope all is well with everyone.

I want to create two fields like below.

Total_OD = count (OD transactions (OD_detail=”OD”)) +count (recovered transactions (OD_detail=”Recovered”))

Total_recovered = count (recovered transactions (OD_detail=”Recovered”))

Total non OD= Non OD transactions (OD_detail =”Not OD”)

Using the field  OD_Details. In the  OD_Details I am Having the values...

'NOT OD','OD' and 'Recovered'

I have tried like this in the Script. But it is not working fine.

load  link_Cust_Cont_Mort,   

     Count(OD_Details='OD')+count(OD_Details='Recovered') as Total_OD,

     Count(OD_Details='Recovered')+count(OD_Details='NOT OD') as Total_Recovered

Resident Transaction1  Group by link_Cust_Cont_Mort;

Will appreciate if someone can assist.

Thanks in Advance.

[atoz1158 · Accepted solution]

Hi

Try the following

load  link_Cust_Cont_Mort,  

     Sum(If(OD_Details='OD',1,0))+Sum(If(OD_Details='Recovered',1,0)) as Total_OD,

     Sum(If(OD_Details='Recovered',1,0))+Sum(If(OD_Details='NOT OD',1,0)) as Total_Recovered

Resident Transaction1  Group by link_Cust_Cont_Mort;

Adrian

[chandra63]

Hi Adrian,

It is really helpful to me. But based on that, I  want to create Variables like below

total_od_recovered_above_90	(Total recovered /total OD) percent is above 90
total_od_recovered_76_90	(Total recovered /total OD) percent is between 76 and 90
total_od_51_75	(Total od/payment tenure) percent is between 51 and 75
total_od_above_90	(Total od/payment tenure) percent is above 90
total_od_recovered_51_75	(Total recovered /total OD) percent is between 51 and 75
total_od_76_90	(Total od/payment tenure) percent is between 76 and 90
total_od_26_50	(Total od/payment tenure) percent is between 26 and 50
total_od_recovered_26_50	(Total recovered /total OD) percent is between 26 and 50
sales_vintage_1	Age of the customer lifecycle is 2
pay_tenure_12	Payment tenure is 12
pay_tenure_24	Payment tenure is 24
total_emi_rlz_26_50	Total (recovered + non od)/payment_tenure percent is between 26 and 50
total_emi_rlz_51_75	Total (recovered + non od)/payment_tenure percent is between 51 and 75

Can we create variables based on that.

please do needful.

Thanks.

[atoz1158 · Accepted solution]

Hi

I am assuming that you really mean flags in each row of a customer type table and not variables as Qlikview means them.

What I would do is along the following lines you would need to adapt these to suit your requirements.

If((Total recovered /total OD)>0.9,1,0) AS total_od_recovered_above_90,

If((Total recovered /total OD)>=0.76 AND (Total recovered /total OD)<=0.9,1,0) AS total_od_recovered_76_90,

and so on for all the others

By using a 1 when the condition is true it means that you can use the Sum() function to say get the number of customers with over 90% recovered as in Sum(total_od_recovered_above_90), I am led to believe that the Sum() function is faster than the Count() function for large datasets.

Hope this Helps

Adrian