topic Re: Qlikview Scripting Scenario in QlikView

Qlikview Scripting Scenario

Thu, 22 Oct 2015 23:19:37 GMT

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

Re: Qlikview Scripting Scenario

jonathandienst — Fri, 23 Oct 2015 05:48:51 GMT

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?

Re: Qlikview Scripting Scenario

Fri, 23 Oct 2015 13:52:33 GMT

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

Re: Qlikview Scripting Scenario

patroser — Fri, 23 Oct 2015 13:57:32 GMT

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

Re: Qlikview Scripting Scenario

Fri, 23 Oct 2015 14:25:41 GMT

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.

Re: Qlikview Scripting Scenario

patroser — Fri, 23 Oct 2015 15:08:36 GMT

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

Re: Qlikview Scripting Scenario

Mon, 26 Oct 2015 13:27:12 GMT

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