Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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);
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));
You can ignore the Remove row stuff, that was only for reading it from this thread...
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 ;
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