topic Re: How to overwrite the duplicate data in qlikview in QlikView

How to overwrite the duplicate data in qlikview

Tue, 17 Nov 2015 12:22:37 GMT

Hi,

I have to overwrite existing data with the new one based on some specific field, how can we do that.

Load * inline

[

Name,Age

A,21

B,22

]

Load * inline

[

Name,Age

A,33

C,22

D,23

]

Final O/P

Name,Age

A,33

B,22

C,22

D,23

Re: How to overwrite the duplicate data in qlikview

maxgro — Tue, 17 Nov 2015 12:27:04 GMT

maybe with a not exists starting from the last table

Load * inline [

Name,Age

A,33

C,22

D,23

];

Load * inline

[

Name,Age

A,21

B,22

]

Where not Exists(Name)

;

Re: How to overwrite the duplicate data in qlikview

Tue, 17 Nov 2015 12:27:59 GMT

Yes, this would work but, in our case we can't change the order of load, since we are creating some variables based on previous load. Please suggest some other solution.

Re: How to overwrite the duplicate data in qlikview

jyothish8807 — Tue, 17 Nov 2015 12:41:28 GMT

Hi navdeep,

Try like this:

Load * inline

[

Name,Age

A,21

B,22

]

Load * inline

[

Name,Age

A,33

C,22

D,23

]

Noconcatenate

load

name,

max(Age) as Age

resident A

group by name;

Drop table A;

Regards

Re: How to overwrite the duplicate data in qlikview

Tue, 17 Nov 2015 12:45:17 GMT

Thanks Joythish,

It's just a demo app, in prod we have lot of duplicates and we don't want this operation only for max value :-). but nice catch, and as mentioned we can't change the order of load.

Re: How to overwrite the duplicate data in qlikview

maxgro — Tue, 17 Nov 2015 12:52:12 GMT

no change in order of load

Load *, -rowno() as id inline

[

Name,Age

A,21

B,22

];

Load *, rowno() as id inline [

Name,Age

A,33

C,22

D,23

];

left join (A)

load Name, max(id) as maxid

Resident A

group by Name;

Tmp: load Name, Age Resident A Where id = maxid;

DROP Table A;

RENAME Table Tmp to A;

Re: How to overwrite the duplicate data in qlikview

flipside — Tue, 17 Nov 2015 13:08:36 GMT

This is how I would do it, but there are various ways ...

Load *, 1 as LoadIndex inline
[
Name,Age
A,21
B,22
E,45
q,10
];

Load *, 2 as LoadIndex inline
[
Name,Age
A,33
C,22
D,23
E,13
Q,11
];

inner join (A)
Load Name, max(LoadIndex) as LoadIndex resident A group by Name;

flipside

Re: How to overwrite the duplicate data in qlikview

NareshGuntur — Tue, 17 Nov 2015 13:10:04 GMT

Hi Navdeep,

Can you try this?

Load * inline

[

Name,Age

A,21

B,22

];

LOAD *,RowNo() as New;

Load * inline

[

Name,Age

A,33

C,22

D,23

];

Concatenate

LOAD * Resident A

Where not Exists(Name);

DROP Table A;

Cheers,

Naresh

Re: How to overwrite the duplicate data in qlikview

Tue, 17 Nov 2015 13:44:11 GMT

Hi,

I am not able to replicate, could you please share the app.

Re: How to overwrite the duplicate data in qlikview

Tue, 17 Nov 2015 13:45:33 GMT

We are not doing any operation on data of table B, could you please explain how it will work ?

Edited- Got it, thanks

Re: How to overwrite the duplicate data in qlikview

jyothish8807 — Tue, 17 Nov 2015 13:56:53 GMT

Hi ,

PFA

Regards