I have follwing script for data load
Policies:
LOAD Month_Year,
POLICY_NO,
// REF_NO,
TRN_DATE,
PERIOD_FROM,
PERIOD_TO,
Interval( PERIOD_TO - PERIOD_FROM,'D')+1as INTERVAL,
//FULL_PERIOD,
NRP ,
(POLICY_NO&'-'&TRN_DATE)AS N
// [NRP per day],
//[mon_bal Days]
FROM
(biff, embedded labels, table is [Correct (2)$]);
pol:
LOAD *,
IF(previous(N)<>N,MonthEnd(TRN_DATE)-TRN_DATE-1,0) AS BAL_DAYS,
Date#(MonthEnd(PERIOD_FROM + IterNo() - 1)) as MonthEnd
resident Policies
While MonthEnd(PERIOD_FROM + IterNo() - 1) <= MonthEnd(PERIOD_TO)
ORDER BY N
;
DROP TABLE Policies;
When I load data with IF(previous(N)<>N,MonthEnd(TRN_DATE)-TRN_DATE-1,0) AS BAL_DAYS, two records are created instead of one record and resulting out put look like this
POLICY_NO TRN_DATE PERIOD_FROM PERIOD_TO INTERVAL MonthEnd BAL_DAYS N
100 7/7/2017 3/22/2017 1/21/2018 306 3/31/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 3/31/2017 24 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 4/30/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 5/31/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 6/30/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 7/31/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 8/31/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 9/30/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 10/31/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 11/30/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 12/31/2017 0 100-42923
100 7/7/2017 3/22/2017 1/21/2018 306 1/31/2018 0 100-42923
I want to omit the first record and balance records should remaim. Kindly help me to do this
