topic Re: COUNT in QlikView

COUNT

upaliwije — Tue, 25 Aug 2015 10:22:18 GMT

Hi Friends

LOAD CLAIM_NO,

PAID_DATE,

RISK,

DUAL( DATE(POL_PERIOD_FROM,'MMM-YY') &' - '& DATE(POL_PERIOD_TO,'MMM-YY'),POL_PERIOD_FROM) AS POLICY_PERIOD

FROM

E:\Qlikview\QVD\FACT_Table\Claims.qvd

(qvd)

WHERE MATCH (CLA_CODE,'MC','M4')

AND YEAR(PAID_DATE)>='2011'

AND MATCH(ACTIVE,'A')

AND Count(DISTINCT CLAIM_NO)>=9

;

I want to load data from the above script subject to condition Count(DISTINCT CLAIM_NO)>=9. But It is not working. Can Some one help me to correct my script to load data as per my requirement please.

Re: COUNT

sinanozdemir — Tue, 25 Aug 2015 10:44:30 GMT

Hi Upali,

Use another preceding load statement:

LOAD

WHERE CLAIM_CNT >=9;

LOAD CLAIM_NO,

PAID_DATE,

RISK,

DUAL( DATE(POL_PERIOD_FROM,'MMM-YY') &' - '& DATE(POL_PERIOD_TO,'MMM-YY'),POL_PERIOD_FROM) AS POLICY_PERIOD,

Count(DISTINCT CLAIM_NO) AS CLAIM_CNT

FROM

E:\Qlikview\QVD\FACT_Table\Claims.qvd

(qvd)

WHERE MATCH (CLA_CODE,'MC','M4')

AND YEAR(PAID_DATE)>='2011'

AND MATCH(ACTIVE,'A')

GROUP BY CLAIM_NO, PAID_DATE, RISK, POL_PERIOD

Re: COUNT

Tue, 25 Aug 2015 10:56:40 GMT

What should 'Count(DISTINCT CLAIM_NO)>=9' exactly check for? The load is processed by each line of data, so if you are trying to count number of claims across multiple records, it may not work. If that's the case, then the claim_no has to be counted before the final load where the condition on claim_no is applied.

mapTable1:

Mapping LOAD

UNIQ_ID,

Count(DISTINCT CLAIM_NO) as CLAIM_NO_counted

FROM

E:\Qlikview\QVD\FACT_Table\Claims.qvd

(qvd) Group By

UNIQ_ID;

Table2:

LOAD

UNIQ_ID,

CLAIM_NO,

PAID_DATE,

RISK,

DUAL( DATE(POL_PERIOD_FROM,'MMM-YY') &' - '& DATE(POL_PERIOD_TO,'MMM-YY'),POL_PERIOD_FROM) AS POLICY_PERIOD

FROM

E:\Qlikview\QVD\FACT_Table\Claims.qvd

(qvd)

WHERE MATCH (CLA_CODE,'MC','M4')

AND YEAR(PAID_DATE)>='2011'

AND MATCH(ACTIVE,'A')

AND ApplyMap('mapTable1', UNIQ_ID, 0) >= 9;

Re: COUNT

upaliwije — Tue, 25 Aug 2015 11:16:30 GMT

Thanks for your reply

But I get following message when loading data

Field not found - <UNIQ_ID>

mapTable1:

Mapping LOAD

UNIQ_ID,

Count(DISTINCT CLAIM_NO) as CLAIM_NO_counted

FROM

E:\Qlikview\QVD\FACT_Table\Claims.qvd

(qvd) Group By

UNIQ_ID

Re: COUNT

Anonymous — Tue, 25 Aug 2015 11:18:58 GMT

Hi Upali,

Use preceeding load for your conditions:

LOAD *

WHERE CLAIM_COUNT >=9;

LOAD CLAIM_NO,

PAID_DATE,

RISK,

DUAL( DATE(POL_PERIOD_FROM,'MMM-YY') &' - '& DATE(POL_PERIOD_TO,'MMM-YY'),POL_PERIOD_FROM) AS POLICY_PERIOD,

Count(DISTINCT CLAIM_NO) AS CLAIM_COUNT

FROM

E:\Qlikview\QVD\FACT_Table\Claims.qvd

(qvd)

WHERE MATCH (CLA_CODE,'MC','M4')

AND YEAR(PAID_DATE)>='2011'

AND MATCH(ACTIVE,'A')

GROUP BY CLAIM_NO, PAID_DATE, RISK, POL_PERIOD;

Instead of:

LOAD CLAIM_NO,

PAID_DATE,

RISK,

DUAL( DATE(POL_PERIOD_FROM,'MMM-YY') &' - '& DATE(POL_PERIOD_TO,'MMM-YY'),POL_PERIOD_FROM) AS POLICY_PERIOD

FROM

E:\Qlikview\QVD\FACT_Table\Claims.qvd

(qvd)

WHERE MATCH (CLA_CODE,'MC','M4')

AND YEAR(PAID_DATE)>='2011'

AND MATCH(ACTIVE,'A')

AND Count(DISTINCT CLAIM_NO)>=9;

Check below post:

Re: having clause in Qlikview

Regards

Neetha

Re: COUNT

Tue, 25 Aug 2015 12:07:05 GMT

I am assuming UNIQ_ID is a field which is the unique identifier in your table. Substitute the field name with the name of the field that is unique identifier of records in your actual table.