Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
@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.
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