Duplicate records in previous function

upaliwije — Fri, 10 Aug 2018 07:49:47 GMT

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

topic Duplicate records in previous function in QlikView

Duplicate records in previous function