Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Two Facts DTL_FCT and T_FCT have small fields PolicyID,ClaimNum
EX: 122|56345
I have Claim_PY_Dates,Amounts in DTL_FCT and Claim_mbr_ids in T_FCT
Scenario:
User will manually enter threshold Amount : Suppose 500
Jan '13-Dec'13 Jan'14-Dec'14
Threshold Input 500
Number of Claim_mbr_ids 10 20
Steps to calculate Number of Claim_mbr_ids above Threshold Input:
1.Calculate sum(Amounts) group by Claim_mbr_id.
2.Count (Claim_mbr_id) where sum(Amounts) group by Claim_mbr_id > User defined Threshold.
Is the Scenario possible to do back end scripting??How??
You want the user to set a threshold and then run a reload -- it may better to do this in the front end. Is there a specific reason that you want to do this in the load script?
Hi Jonathan,
Thanks for your reply.
How can we achieve this logic in the front end scenario ??Please,provide me the sample if you have any??
Please,suggest me any other thoughts??
Depending on how many different user inputs you need to have I would suggest you:
a) define a variable for setting the threshold. You could then work with this variable in the front end, or you could also take this variable into account within your next reload.
b) define inputfields if you need to define separate thresholds for i.e. separate departments
Patrick
Patrick,
Thanks for the reply.
I understood create a variable for user threshold.But,
How to compute the below logic based on threshold in the front end??
Steps to calculate Number of Claim_mbr_ids above Threshold Input:
1.Calculate sum(Amounts) group by Claim_mbr_id.
2.Count (Claim_mbr_id) where sum(Amounts) group by Claim_mbr_id > User defined Threshold.
Please,provide the sample if any any.
The easiest way would be something like:
count(if(aggr(sum(Amounts) > vTreshold, Claim_mbr_id), 1))
In this case you get beack the total number of claim_mbr_id's where the sum of amounts is bigger than the treshold
But I will overthink this solution, if there is a chance to avoid aggr in the frontend.
Patrick
Thanks Patrick for the response.
Two Facts DTL_FCT and T_FCT have small fields PolicyID,ClaimNum and create linktable name(pol_clm) for those two commonfields in qlikview.
EX: 122|56345
I have Claim_PY_Dates,Amounts in DTL_FCT and Claim_mbr_ids in T_FCT. How to join these in qlikview from two different tables and create a conditional flag on Claim_mbr_ids.Claim_mbr_ids starts with sequence numbers (1000-10000) in to one group and (20000-30000) into other group.
1.Calculate sum(Amounts) group by conditional flag of Claim_mbr_ids??.
Please,suggest how to develop in back end script ?? in a transform layer to make the make front end calculations easier??Please,provide sample if any??