Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

How to insert different Id based on a KeyField Challenge

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 :

Id1.PNG

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 :

Id2.PNG

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.

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: How to insert different Id based on a KeyField Challenge

@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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

View solution in original post

2 Replies
Highlighted
Specialist III
Specialist III

Re: How to insert different Id based on a KeyField Challenge

@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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

View solution in original post

Highlighted
Partner
Partner

Re: How to insert different Id based on a KeyField Challenge

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

Best regards, Marcel.