Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Duplicate records in previous function

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

0 Replies