topic Re: Modify data based on duplicate entry in QlikView

Modify data based on duplicate entry

Wed, 25 Nov 2020 16:16:04 GMT

Hi Gurus,

Need help as I am lost on how to do this.

I have a table which has following structure and data:

UniqID	JulianMonth	FLAG	Status
101-24201	24201	A	Active
101-24202	24202	N	Active
101-24203	24203	C	Closed
101-24204	24204	N	Closed
101-24205	24205	N	Closed
101-24206	24206	A	Active
102-24201	24201	A	Active
102-24202	24202	N	Active

Business rule is to have only first entry of Flag and ignore all to mark as 'N' for all UniqueIDs. I developed code to generate flag based on first entry using PEEK to compare with previous row. I want to convert all A or C to N after their first instance.

Result set should look like this:

UniqID	JulianMonth	FLAG	Status
101-24201	24201	A	Active
101-24202	24202	N	Active
101-24203	24203	C	Closed
101-24204	24204	N	Closed
101-24205	24205	N	Closed
101-24206	24206	N	Active
102-24201	24201	A	Active
102-24202	24202	N	Active

I am quite lost on how to identify one value duplicate and change the value grouping at UniqIDs. Appreciate if anyone can guide. Thanks.

Re: Modify data based on duplicate entry

Kushal_Chawda — Fri, 28 Jul 2017 07:24:39 GMT

Data:

LOAD RowNo() as Row,

UniqID,

FLAG,

Status

FROM

[https://community.qlik.com/thread/269304]

(html, codepage is 1252, embedded labels, table is @1);

New:

LOAD *,

if(UniqID=Previous(UniqID) and FLAG='N' and Peek(NewFlag)=1,Peek(NewFlag)+1,0) as NewFlag2;

LOAD UniqID,

Row,

FLAG,

if(UniqID=Previous(UniqID) and FLAG='N',1,0) as NewFlag,

Status

Resident Data

Order by Row;

DROP Table Data;

Final:

NoConcatenate

LOAD *,

if(UniqID=Previous(UniqID) and FLAG<>'N' and Previous(NewFlag2)=2,'N',FLAG) as NEWFLAG

Resident New;

DROP Table New;

drop Fields NewFlag2,NewFlag;

Re: Modify data based on duplicate entry

Kushal_Chawda — Fri, 28 Jul 2017 07:28:03 GMT

You need to order your Data properly, I think you need to oder your data by Unique iD & Date, here you don't have Date so I followed the actual load

Re: Modify data based on duplicate entry

Mon, 31 Jul 2017 05:38:20 GMT

Hi Kushal,

Thanks. I tried the code but it has not removed any rows and generated 4 duplicates rows for each row with flag value N. To generate more uniqueness, I generated new UniqueIDs which is a combination of UniqueID+JulianMonth. This create unique ID for each row. Now UniqueID looks like this:

Previous UniqueID: 100, 100, 100, 101, 102 etc..

Now new UniqueID: 100-2402, 100-2403, 100-2404,101-2403 etc..

I also have Julian month for every row now which is not duplicated i.e. one row belong to one julian month only.

Please could you guide.

Re: Modify data based on duplicate entry

Kushal_Chawda — Mon, 31 Jul 2017 06:54:10 GMT

Can you share sample data?

Re: Modify data based on duplicate entry

Mon, 31 Jul 2017 07:01:12 GMT

Hi Kushal,

I updated the data in main post. Result-set of your code is same data with repeat of all data 4 times where FLAG = 'N'

Re: Modify data based on duplicate entry

Mon, 31 Jul 2017 23:53:41 GMT

Hi Guru's

Please help. I am lost