How to insert different Id based on a KeyField Challenge

[marcel_olmo]

Hi guys,

I have an Input Table with some raw data where I have Ids based on autonumber and some fields that looks like this :

and I'd like to change the Id that is related with a "keyField" because it means that it begins a new slot of a product. My desired result would be something like this :

How could you do this? Could you help me?

I've attached this example in an excel with the input and the desiredOutputTable.

Best regards, Marcel.

[Taoufiq_Zarra · Accepted solution]

@marcel_olmo 

I made a quick response, this script can be optimised.

Data:
LOAD autonumber(Id&Field) as ID1, * INLINE [
    Id, Field
    100, OtherField1
    101, KeyField
    102, OtherField1
    103, OtherField1
    104, KeyField
    105, OtherField1
    106, KeyField
    107, OtherField1
    108, OtherField1
    109, KeyField
    110, OtherField1
];



Temp:
noconcatenate

load rowno() as ID2,* resident Data where Field='KeyField';
join load * resident Data;
drop table Data;

Data2:
noconcatenate
load * resident Temp order by Id;
drop table Temp;
let a=peek('Id',0);
output:
noconcatenate
load *,if(ID1=1,'$(a)',if(isnull(ID2),peek('IDtmp'),ID2)) as IDtmp resident Data2;
drop table Data2;
drop field ID1,Id,ID2;
rename field IDtmp to Id;

ouput:

[marcel_olmo]

Thanks @Taoufiq_Zarra that's what I was looking for and it worked fine.

Best regards, Marcel.