if the value is exists - how to check on row wise data

[#1]

I have data is coming from table

Key	Code
HS000	1
HS000H2000
HS000H2000H2001
HS000H2000H2002
HS000H2000H2003
HS000H2000H2998
HS000H2000H2999
HS000H3000
MP000	2
MP000MPAA0
MP000MPAA0MPAA1
MP000MPAA0MPAA2
MP000MPAA0MPAA3
LS00A	1
LS00AA1000	1
LS00AA1000A1001
LS00AA1000A1002
CNE00	3
CNE00CNEA0
CNE00CNEA0CNEA1

I would like to see the data for reporting  - checking the first 5 character or 10 character if the value exists in the Code column - I have to update the missing rows for the code.

Expecting   Results
Key	Code
HS000	1
HS000H2000	1
HS000H2000H2001	1
HS000H2000H2002	1
HS000H2000H2003	1
HS000H2000H2998	1
HS000H2000H2999	1
HS000H3000	1
MP000	2
MP000MPAA0	2
MP000MPAA0MPAA1	2
MP000MPAA0MPAA2	2
MP000MPAA0MPAA3	2
LS00A	1
LS00AA1000	1
LS00AA1000A1001	1
LS00AA1000A1002	1
CNE00	3
CNE00CNEA0	3
CNE00CNEA0CNEA1	3

Advance thanks for your help.

[CELAMBARASAN]

Hi,

     Check with autonumber().use something like

     Autonumber(Left(Key,5)) as Code

Celambarasan

[#3]

autonumber will not work.Code has differen value 151,143,266 etc.It will not work .Example - I have shown the code as 1,2,3

[CELAMBARASAN]

Hi,

     Then check with this

     If(Left(Key,5)<>Previous(Left(Key,5)),Code,Previous(Code)) as Code

Celambarsan

Message was edited by: Celambarasan Adhimulam

[#5]

hi, if(Left(key,5) ='HS000' or Left(key,5) ='LS00A',1,if(Left(key,5) ='MP000',2, if(Left(key,5) ='CNE00',3))) as code

[#6]

I have to deal with 5000 records .It is not easy to check and apply the condition one by one.I am looking for dynamically check and pickup the value

[jansen28]

You can perform it in the load script

Hierarchy (Key,Parent_key,Code,,,,,Depth)

LOAD Key,

     Mid(Key,1,5) as Parent_key,

     Code

FROM

$(v_Home_Path)\Qlikview Community\if the value is exists - how to check on row wise data\Test Data.xlsx

(ooxml, embedded labels, table is Sheet1);

And in your tables, you can use Key and Code1 as the fields