How to make a group data issue

[marcel_olmo]

Hi guys,

I'm trying to do an app where I have the following table :

where x has to be replace by 1,2 and 3 with a loop, to get this desired result :

How can I get this?

As you can see, you can have 3 characters plus an X or 4 characters without the X and you have to do nothing because you have the complete group code of 4 digits.

I've attached an excel table with the example

Best regards, Marcel.

[Taoufiq_Zarra]

I do it quickly so you can optimize it ....

Data:

LOAD * INLINE [
    Id, Group
    101x, Group 1
    1021, Group 2
    103x, Group 3
];

TempX:
noconcatenate
load * resident Data where wildmatch(Id,'*x')>0;
TempNX:
noconcatenate
load Id as I, "Group" as G resident Data where wildmatch(Id,'*x')=0;

drop table Data;


FOR i = 0 TO NoOfRows('TempX') - 1
Temp:
concatenate
LET IDtemp = keepchar(peek('Id', $(i), 'TempX'),'0123456789');
LET Grouptemp = peek('Group', $(i), 'TempX');

	FOR j = 1 TO 3
			
			LOAD '$(IDtemp)$(j)' as ID,'$(Grouptemp)' as "Group"
			RESIDENT TempX
			;

	NEXT j
	
NEXT i
concatenate
load I as ID, G as "Group" resident TempNX;

Final:
noconcatenate
load distinct * resident Temp;

drop table TempX,TempNX,Temp;

Result:

[marcel_olmo]

Thanks @Taoufiq_Zarra ,

I've copy your code and I've got this :

Could you add your example in QlikView or Qlik Sense please?

Best regards, Marcel.

[Taoufiq_Zarra · Accepted solution]

Hi,

attached Qlikview File

[marcel_olmo]

Thanks! If I comment the concatenate it works fine.

Best regards, Marcel.

[Rao_007]

Replace(Class(id),'x','1,2,3,1,2,3')