COUNT

[upaliwije]

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.

[sinanozdemir]

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

[#3]

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;

[upaliwije]

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

[Anonymous]

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

[#6]

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.