topic Re: update records in QlikView

update records

sanjujeeboy — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

i have 2 tables as shown below:

my output should be table C: which has all codes from 10 to 20 (code 12 should have updated values from table B)

A:
Code	2017	2018	2019
10	411	760	120
11	864	639	688
12	118	900	471
13	229	369	513
14	840	311	862
15	489	560	426
16	718	771	155
17	217	203	597



B:
Code	2017	2018	2019

12	234	678	564
18	123	432	453
19	345	0	0
20	229	369	513

Re: update records

stigchel — Tue, 17 Apr 2018 13:11:28 GMT

Something like this (edit, qvw attached)

LOAD Code,

[2017],

[2018],

[2019]

FROM

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

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 14)),

Remove(Row, Pos(Top, 13)),

Remove(Row, Pos(Top, 12)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 10)),

Remove(Row, Pos(Top, 9)),

Remove(Row, Pos(Top, 8)),

Remove(Row, Pos(Top, 7)),

Remove(Row, Pos(Top, 6)),

Remove(Row, Pos(Top, 5)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 1))

));

Concatenate LOAD Code,

[2017],

[2018],

[2019]

FROM

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

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 20)),

Remove(Row, Pos(Top, 19)),

Remove(Row, Pos(Top, 18)),

Remove(Row, Pos(Top, 17)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 15)),

Remove(Row, Pos(Top, 14)),

Remove(Row, Pos(Top, 13)),

Remove(Row, Pos(Top, 12)),

Remove(Row, Pos(Top, 11)),

Remove(Row, Pos(Top, 1))

)) Where not Exists (Code);

Re: update records

jaumecf23 — Tue, 17 Apr 2018 13:12:05 GMT

You need to use the Exists function. Here you have an example of your case:

LOAD * INLINE [

Code, 2017, 2018, 2019

12, 234, 678, 564

18, 123, 432, 453

19, 345, 0, 0

20, 229, 369, 513

];

LOAD * INLINE [

Code, 2017, 2018, 2019

10, 411, 760, 120

11, 864, 639, 688

12, 118, 900, 471

13, 229, 369, 513

14, 840, 311, 862

15, 489, 560, 426

16, 718, 771, 155

17, 217, 203, 597

] where not(Exists(Code));

Re: update records

stigchel — Tue, 17 Apr 2018 13:13:45 GMT

You can ignore the Remove row stuff, that was only for reading it from this thread...

Re: update records

tripatirao — Tue, 17 Apr 2018 13:17:39 GMT

LOAD Code,

[2017],

[2018],

[2019]

FROM

(ooxml, embedded labels, table is Data)

Where Code<>12 ;

Concatenate

LOAD Code,

[2017],

[2018],

[2019]

FROM

(ooxml, embedded labels, table is Sheet2)

Where Code=12 ;

Re: update records

YoussefBelloum — Tue, 17 Apr 2018 14:24:14 GMT

Hi,

you can do something like this:

LOAD *,RowNo() as RowNum ,'previous' as FLAG

Inline [

Code, 2017, 2018, 2019

10, 411, 760, 120

11, 864, 639, 688

12, 118, 900, 471

13, 229, 369, 513

14, 840, 311, 862

15, 489, 560, 426

16, 718, 771, 155

17, 217, 203, 597

];

Concatenate

LOAD *,Date(Today()) as FLAG

Inline [

Code, 2017, 2018, 2019

12, 234, 678, 564

18, 123, 432, 453

19, 345, 0, 0

20, 229, 369, 513

];

temp:

LOAD *, AutoNumber(Code) as ID

Resident A;

DROP Table A;

temp1:

LOAD Code, COUNT(ID) as occurence

Resident temp

Group by Code, ID;

Join(temp)

LOAD *

Resident temp1;

final_table:

NoConcatenate

LOAD * Resident temp

Where (occurence=2 and FLAG=Date(Today())) or occurence=1;

DROP Table temp;

DROP Table temp1;

PFA