topic Re: Second value of same id using Apply map in QlikView

Removed

heena_shaikh — Wed, 14 Aug 2019 07:03:41 GMT

Re: Second value of same id using Apply map

ali_hijazi — Tue, 13 Jun 2017 09:32:53 GMT

suppose your original table is called T

then do the following:

T2:

mapping

load Column1, FirstSortedValue(Column2,-Column2)

resident T

group by Column1;

drop table T;

hope this helps

Removed

heena_shaikh — Wed, 14 Aug 2019 07:03:11 GMT

Re: Second value of same id using Apply map

swuehl — Tue, 13 Jun 2017 09:46:57 GMT

You could create a mapping table with all values by adding a order information part delimited by a arbitrary delimiter not part of your key values ('|' in my sample). Then you can address first, second, etc mapping value per key:

MAP:

MAPPING

LOAD A&'|'&AutoNumber(Recno(),A) as A, B

INLINE

[

A B

1 A

1 B

2 C

3 D

3 E

4 F

] (delimiter is '\t');

LOAD A,

Applymap('MAP', A&'|2', ApplyMap('MAP',A&'|1')) as Test

INLINE

[

];

Removed

heena_shaikh — Wed, 14 Aug 2019 07:02:41 GMT

Re: Second value of same id using Apply map

tresB — Tue, 13 Jun 2017 10:00:15 GMT

It's because you are coy-pasting it and '\t' is not working properly in that case. Try using different (comma) separator like:

MAP:

MAPPING

LOAD A&'|'&AutoNumber(Recno(),A) as A, B

INLINE

[

A, B

1, A

1, B

2, C

3, D

3, E

4, F

];

LOAD A,

Applymap('MAP', A&'|2', ApplyMap('MAP',A&'|1')) as Test

INLINE

[

];

Re: Second value of same id using Apply map

Peter_Cammaert — Tue, 13 Jun 2017 10:07:02 GMT

There are no tab characters between column headers and field values. Either replace the spaces with a tab character, or change this

(delimiter is '\t');

to this

(delimiter is ' ');

Re: Second value of same id using Apply map

ali_hijazi — Tue, 13 Jun 2017 10:17:23 GMT

from the sample data you supplied you can take the max group by first column and do the mapping table

Re: Second value of same id using Apply map

antoniotiman — Tue, 13 Jun 2017 10:47:53 GMT

Hi,

maybe this

MapTable:
Mapping
LOAD A,LastValue(B) as B Inline [
A, B
1, A
1, B
2, C
3, D
3, E
4, F]
Group By A;

LOAD DISTINCT A,ApplyMap('MapTable',A) as B Inline [
A, C
1, 12
1, 23
2, 34
3, 45
3, 56
4, 67];

Regards,

Antonio