topic Re: Get first ID from the data in QlikView

Get first ID from the data

udaya_kumar — Tue, 12 Jul 2016 08:55:34 GMT

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?

Re: Get first ID from the data

martinpohl — Tue, 12 Jul 2016 08:59:17 GMT

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

Re: Get first ID from the data

lironbaram — Tue, 12 Jul 2016 09:03:21 GMT

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;

Re: Get first ID from the data

MindaugasBacius — Tue, 12 Jul 2016 09:18:35 GMT

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:

Re: Get first ID from the data

antoniotiman — Tue, 12 Jul 2016 09:49:08 GMT

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