topic Re: Upload new data and replace old one in QlikView

Upload new data and replace old one

yura_ratu — Fri, 22 Aug 2014 10:58:15 GMT

Hi all,

I load new data every day and need not just concatenate it to previous one, but also make changes in old data according to a new data. Here is an example of tables

load * Inline [

a, b

1,2

2,3

3,8

];

load * Inline [

a, b

1,5

2,3

4,3

5,4

];

As a result of joining/concatenating them I would like to have

a, b

1,5

2,3

3,8

4,3

5,4

How can I do that?

Re: Upload new data and replace old one

alexandros17 — Fri, 22 Aug 2014 11:04:41 GMT

Here it is:

NoConcatenate

load * Inline [

a, b

1,2

2,3

3,8

];

NoConcatenate

load * Inline [

a, b

1,5

2,3

4,3

5,4

];

AB:

NoConcatenate

LOAD * Resident A;

Concatenate

LOAD * Resident B;

DROP Tables A, B;

Left Join

LOAD a, Max(b) as M Resident AB Group by a;

Final:

NoConcatenate

LOAD * Resident AB Where M = b;

DROP Table AB;

Re: Upload new data and replace old one

Fri, 22 Aug 2014 11:12:02 GMT

Can you try this

load * Inline [

a, b

1,2

2,3

3,8

];

Concatenate

load * Inline [

a, b

1,5

2,3

4,3

5,4

];

NoConcatenate

AB:

load distinct a, b

Resident A;

drop table A;

Re: Upload new data and replace old one

yura_ratu — Fri, 22 Aug 2014 11:13:23 GMT

Thanks Alessandro!

But what if values are not numbers, or number which become both less or more. In final table should be value from second load, not matter what there

Re: Upload new data and replace old one

yura_ratu — Fri, 22 Aug 2014 11:16:18 GMT

Result will be

1,2

1,5

2,3

3,8

4,3

5,4

Re: Upload new data and replace old one

nagaiank — Fri, 22 Aug 2014 11:20:28 GMT

Try loading in the following order:

load * Inline [

a, b

1,5

2,3

4,3

5,4

];

Concatenate

load * Inline [

a, b

1,2

2,3

3,8

]

where not exists(a);

Re: Upload new data and replace old one

Fri, 22 Aug 2014 11:20:33 GMT

You mean the result should be Ordered??

Re: Re: Upload new data and replace old one

Fri, 22 Aug 2014 11:27:09 GMT

I get exactly the way you asked for the code snippet post my me earlier. Plz. see the screen shot.

Re: Upload new data and replace old one

yura_ratu — Fri, 22 Aug 2014 11:32:16 GMT

I meant that should be unique "a" values, and "b" values from the latest load

Re: Upload new data and replace old one

yura_ratu — Fri, 22 Aug 2014 11:35:16 GMT

It works! Thank you.

Could you please explain how this "where not exists(a)" statement works here?