Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
sanjujeeboy
Creator
Creator

update records

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:
Code201720182019
10411760120
11864639688
12118900471
13229369513
14840311862
15489560426
16718771155
17217203597
B:
Code201720182019
12234678564
18123432453
1934500
20229369513
5 Replies
stigchel
Partner
Partner

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
Creator III
Creator III

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

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

tripatirao
Creator II
Creator II

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

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