Get first ID from the data

[udaya_kumar]

Hi,

I have a data like below

ID1	ID2
1	1
1	2
1	3
1	4
2	7
2	8
2	9
3	10
3	11
3	12

I want the output to look like

ID1	ID2
1	1
1	1
1	1
1	1
2	7
2	7
2	7
3	10
3	10
3	10

ID1 is having values like 1,2,3 and ID2 is having values like 1,2,3,4,7,8,9,10,11,12

ID1 (ID1 = 1) has mapped to ID2 (1,2,3,4)

ID1 (ID1 = 2) has mapped to ID2 (7,8,9)

ID1 (ID1 = 3) has mapped to ID2 (10,11,12)

I want the values of ID2 1,2,3,4 to have values like 1,1,1,1

For every combination of ID1, take the first value from ID2 and assign it to other values of ID1.

How to get that in the script level?

[martinpohl]

so load like this:

load

ID1,

ID2 as ID2temp

from yoursource;

left join

load

ID1,

min(ID2temp) as ID2

Group by ID1;

drop field ID2temp;

Regards

[lironbaram · Accepted solution]

hi

this script will answer your requirement

TempData:

load * inline [

ID1 ,ID2

1 ,1

1, 2

1 ,3

1 ,4

2 ,7

2 ,8

2 ,9

3 ,10

3 ,11

3, 12

];

Data:

load rowno() As RecordID,

  ID1,

  if(ID1=Previous(ID1),peek('ID3'),ID2) AS ID3

Resident TempData

order by ID1,ID2;

drop Table TempData;

RENAME Field ID3 to ID2;

[MindaugasBacius]

Also, you can make like this:

map_Table:
mapping LOAD ID1
     ,ID2
FROM
[https://community.qlik.com/thread/224657]
(html, codepage is 1257, embedded labels, table is @1)
where ID1 <> previous(ID1);


Table:
LOAD ID1
     ,applymap('map_Table', ID1, ':') as ID2
     ,rowno()
FROM
[https://community.qlik.com/thread/224657]
(html, codepage is 1257, embedded labels, table is @1);

The result and attachment are below:

[antoniotiman]

Temp:

Load ID1,ID2 ..............

Table:
NoConcatenate
LOAD RowNo() as Row,  //Optional
  ID1,
If(ID1=Peek('ID1'),Peek('ID2'),ID2) as ID2
Resident Temp
Order By ID1
;
Drop Table Temp;

Note : If You Want The First Value of ID2 then

Order By ID1.

If You Want Min(Value) of ID2 then

Order By ID1,ID2