topic Re: Re: How to avoid duplicate records in qlikview in QlikView

How to avoid duplicate records in qlikview

Tue, 22 Apr 2014 16:15:57 GMT

Hi Team,

Thanks in Advance,

I need help on below requirement.

Please find the attached document for the reference and how to implement in qlikview.

Thanks,

Re: How to avoid duplicate records in qlikview

MK_QSL — Tue, 22 Apr 2014 16:33:48 GMT

LOAD ID,

[update date1],

type1

FROM

Book1.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where [update date1] <> '-';

Left Join

LOAD ID,

[updated date2],

type2

FROM

Book1.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where [updated date2] <> '-';

Re: How to avoid duplicate records in qlikview

Tue, 22 Apr 2014 18:00:08 GMT

Hi Manish,

Thanks for reply,

I was tried your code and getting below error:

Field not found<update date1> and <update date2>

Please help me on this

Regards,

Re: Re: How to avoid duplicate records in qlikview

Tue, 22 Apr 2014 18:02:15 GMT

Hi,

Please find the attached script which I used it.

Regards,

Re: How to avoid duplicate records in qlikview

its_anandrjs — Tue, 22 Apr 2014 18:30:19 GMT

Try to load like this

Tab1:

LOAD * INLINE [

ID, update date1, updated date2, type1, type2

123, 3/12/2012, -, X, -

123, -, 3/15/2012, , Y

234, 4/20/2012, -, X, -

234, -, 4/25/2012, , Y];

NoConcatenate

New:

LOAD

ID,

[update date1],

type1

Resident Tab1

Where [update date1] <> '-';

Left Join

LOAD

ID,

[updated date2],

type2

Resident Tab1

Where [updated date2] <> '-';

DROP Table Tab1;

And use correct field name from your source.

Regards

Re: Re: How to avoid duplicate records in qlikview

maxgro — Tue, 22 Apr 2014 18:53:08 GMT

If you want just a n ID by group, I think you can try with a simple group by

Tab1:

load ID,

if(trim([update date1])='-', null(), [update date1]) as [update date1],

if(trim([update date2])='-', null(), [update date2]) as [update date2],

type1,

type2;

LOAD * INLINE [

ID, update date1, update date2, type1, type2

123, 12/03/2012, -, X, -

123, -, 15/03/2012, , Y

234, 20/04/2012, -, X, -

234, -, 25/04/2012, , Y];

Tab:

NoConcatenate

load

ID,

Max([update date1]) as [update date1],

Max([update date2]) as [update date2],

MaxString(type1) as type1,

MaxString(type2) as type2

Resident

Tab1

Group by ID;

DROP Table Tab1;

Re: How to avoid duplicate records in qlikview

MK_QSL — Tue, 22 Apr 2014 18:54:26 GMT

Check your Excel File. It may be possible, that the fieldname you have provided to us is different than your actual file...