Re: update records - Qlik Community

sanjujeeboy · ‎2018-04-17

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

stigchel · ‎2018-04-17

Something like this (edit, qvw attached)

B:

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))

));

A:

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);

jaumecf23 · ‎2018-04-17

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

B:

LOAD * INLINE [

Code, 2017, 2018, 2019

12, 234, 678, 564

18, 123, 432, 453

19, 345, 0, 0

20, 229, 369, 513

];

A:

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));

stigchel · ‎2018-04-17

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

tripatirao · ‎2018-04-17

A:

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 ;

YoussefBelloum · ‎2018-04-17

Hi,

you can do something like this:

A:

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

B:

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