Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

udaya_kumar
Valued Contributor

Get first ID from the data

Hi,

I have a data like below

ID1ID2
11
12
13
14
27
28
29
310
311
312

I want the output to look like

ID1ID2
11
11
11
11
27
27
27
310
310
310

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?


1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

Re: Get first ID from the data

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;

4 Replies
martinpohl
Valued Contributor II

Re: Get first ID from the data

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
Honored Contributor II

Re: Get first ID from the data

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
Valued Contributor II

Re: Get first ID from the data

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:

Screenshot_1.jpg

antoniotiman
Honored Contributor III

Re: Get first ID from the data

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

Community Browser