Qlikview Scripting Scenario

[#1]

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??

[jonathandienst]

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?

[#3]

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??

[patroser]

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

[#5]

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.

[patroser]

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

[#7]

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??