Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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
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;
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:
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