Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
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
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:
Regards
Neetha
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.