Values of another dimension

[P_Kumar]

Hi,

I have following data set and requirement:

S.No	Category	Code_Main	Code_1	Code_2	Code_3	Code_4
1	AE06	123	789	675	567	435
2	KE01	234	765	897	767	429
3	JE07	122	436	768	890	784
4	BE23	567	908	777	876	129

- When category='AE06', then the corresponding row should show the values of (Code_Main)-1 values for Code_Main, Code_1 and Code_2 columns and rest  remain should be same.  So effectively , after implementation, we shouldn't find Code_Main=123 and result should look like following.

S.No	Category	Code_Main	Code_1	Code_2	Code_3	Code_4
1	AE06	122	436	768	567	435
2	KE01	234	765	897	767	429
3	JE07	122	436	768	890	784
4	BE23	567	908	777	876	129

Can some please help with logic and syntax to be used please.  Thanks.

[Taoufiq_Zarra]

@P_Kumar 

May be :

Data:

load * Inline [
S.No,Category,Code_Main,Code_1,Code_2,Code_3,Code_4
1,AE06,123,789,675,567,435
2,KE01,234,765,897,767,429
3,JE07,122,436,768,890,784
4,BE23,567,908,777,876,129
];

Mapping1:
mapping LOAD distinct Code_Main, Code_1 as CC1 resident Data;

Mapping2:
mapping LOAD distinct Code_Main, Code_2 as CC2 resident Data;


output:
NoConcatenate

load 
S.No,
Category,
if(Category='AE06',Code_Main-1,Code_Main) as Code_Main,
if(Category='AE06',ApplyMap('Mapping1', Code_Main-1),Code_1) as Code_1,
if(Category='AE06',ApplyMap('Mapping2', Code_Main-1),Code_2) as Code_2,
Code_3,
Code_4
Resident Data;

drop table Data;

output :

attached qvf file

[P_Kumar]

@Taoufiq_Zarra  - thanks a lot for your solution.  It's working fine.  

Since I have copied the required information from S.No#3, I do't need that row any more.  How could delete the highlighted row from load.

[Taoufiq_Zarra · Accepted solution]

@P_Kumar 

May be this ?

Data:

load * Inline [
S.No,Category,Code_Main,Code_1,Code_2,Code_3,Code_4
1,AE06,123,789,675,567,435
2,KE01,234,765,897,767,429
3,JE07,122,436,768,890,784
4,BE23,567,908,777,876,129
];

Mapping1:
mapping LOAD distinct Code_Main, Code_1 as CC1 resident Data;

Mapping2:
mapping LOAD distinct Code_Main, Code_2 as CC2 resident Data;

Mapping3:
mapping LOAD distinct Code_Main, S.No as CC3 resident Data;

Tmp:
NoConcatenate

load 
S.No,
Category,
if(Category='AE06',Code_Main-1,Code_Main) as Code_Main,
if(Category='AE06',ApplyMap('Mapping1', Code_Main-1),Code_1) as Code_1,
if(Category='AE06',ApplyMap('Mapping2', Code_Main-1),Code_2) as Code_2,
Code_3,
Code_4,
if(Category='AE06',ApplyMap('Mapping3', Code_Main-1),0) as DeleteYN
Resident Data;

todelete:

load distinct DeleteYN as A resident Tmp where DeleteYN>0;


output:
NoConcatenate
load * resident Tmp where not Exists(A,[S.No]);


drop table Data,Tmp,todelete;
drop fields DeleteYN;

output :

attached qvf file