Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have one mapping table like
Filterid | Rfsid |
Rf01Pc01 | Rat111 |
Rf02Pc02 | Rat222 |
Rf03Pc03 | Rat333 |
Cf01 | ABT1 |
Cf02 | ABT2 |
Cf03 | ABT3 |
and first source table like
Filterid | Pcflag | Name |
Rf01 | Pc01 | Akash |
Rf02 | Pc02 | Avinash |
Rf03 | Pc03 | Srinu |
and second table like .Here Pcflag field is not there in second source.
Filterid | Name |
Cf01 | Raj |
Cf02 | Raja |
Cf03 | Vivek |
by using single load statement how can i get output like this:-
Filterid&Pcflag | Applay | Name |
Rf01Pc01 | Rat111 | Akash |
Rf02Pc02 | Rat222 | Avinash |
Rf03Pc03 | Rat333 | Srinu |
Cf01 | ABT1 | Raj |
Cf02 | ABT2 | Raja |
Cf03 | ABT3 | Vivek |
Hi,
Pcflag is not available in the 4 the sheet..
May be try this script..
SET ERRORMODE=0;
DIRECTORY ;
T1:
mapping
LOAD Filterid,
Rfsid
FROM
[Applay Map.xlsx]
(ooxml, embedded labels, table is Sheet1);
Temp:
LOAD * Inline [
Temp
];
for each vsheet in 'Sheet4','Sheet3';
temp_1:
LOAD * FROM [Applay Map.xlsx]
(ooxml, embedded labels, table is [$(vsheet)]);
LET x = FieldNumber('Pcflag','temp_1');
if $(x) = 0 then
Concatenate(temp_1)
LOAD * Inline [
Pcflag
];
end if
Concatenate(Temp)
LOAD
Filterid&Pcflag,
ApplyMap('T1',Filterid&Pcflag,'') as Applay,
Pcflag,
Name
Resident temp_1;
DROP Table temp_1;
next vsheet;
DROP Fields Temp;
Try this..
MAP:
Mapping
LOAD * INLINE [
Filterid, Rfsid
Rf01Pc01, Rat111
Rf02Pc02, Rat222
Rf03Pc03, Rat333
Cf01, ABT1
Cf02, ABT2
Cf03, ABT3
];
T1:
LOAD * INLINE [
Filterid, Pcflag, Name
Rf01, Pc01, Akash
Rf02, Pc02, Avinash
Rf03, Pc03, Srinu
];
Concatenate
LOAD * INLINE [
Filterid, Name
Cf01, Raj
Cf02, Raja
Cf03, Vivek
];
NoConcatenate
Final:
LOAD Filterid&Pcflag as Filter_Flag,Name, ApplyMap('MAP',Filterid&Pcflag,'Unknown') as Map_Field Resident T1;
DROP Table T1;
Hi,
I want load Single load statement.
In Excel sheet1 having this data
Filterid, Pcflag, Name
Rf01, Pc01, Akash
Rf02, Pc02, Avinash
Rf03, Pc03, Srinu
and sheet2 having this data
Filterid, Name
Cf01, Raj
Cf02, Raja
Cf03, Vivek
i tried like this in my script:-
T1:
mapping
LOAD Filterid,
Rfsid
FROM
(ooxml, embedded labels, table is Sheet1);
for each vsheet in 'Sheet4','Sheet3';
T2:
LOAD
Filterid&Pcflag,
ApplyMap('T1',Filterid&Pcflag,'') as Applay,
Pcflag,
Name
FROM
(ooxml, embedded labels, table is [$(vsheet)]);
next vsheet;
T1:
LOAD * INLINE [
Filterid, Pcflag, Name
Rf01, Pc01, Akash
Rf02, Pc02, Avinash
Rf03, Pc03, Srinu
];
Concatenate
LOAD * INLINE [
Filterid, Name
Cf01, Raj
Cf02, Raja
Cf03, Vivek
];
MAP:
LOAD left(trim(Filterid),4) as Filterid,
Rfsid ;
LOAD * INLINE [
Filterid, Rfsid
Rf01Pc01, Rat111
Rf02Pc02, Rat222
Rf03Pc03, Rat333
Cf01, ABT1
Cf02, ABT2
Cf03, ABT3
];
left join (MAP)
LOAD *
Resident T1;
drop table T1;
Hi,
Please find the my source file.
Regards,
mahesh
Hi Kushal,
Please find my attached file.
Regards,
Mahesh
Hi,
Pcflag is not available in the 4 the sheet..
May be try this script..
SET ERRORMODE=0;
DIRECTORY ;
T1:
mapping
LOAD Filterid,
Rfsid
FROM
[Applay Map.xlsx]
(ooxml, embedded labels, table is Sheet1);
Temp:
LOAD * Inline [
Temp
];
for each vsheet in 'Sheet4','Sheet3';
temp_1:
LOAD * FROM [Applay Map.xlsx]
(ooxml, embedded labels, table is [$(vsheet)]);
LET x = FieldNumber('Pcflag','temp_1');
if $(x) = 0 then
Concatenate(temp_1)
LOAD * Inline [
Pcflag
];
end if
Concatenate(Temp)
LOAD
Filterid&Pcflag,
ApplyMap('T1',Filterid&Pcflag,'') as Applay,
Pcflag,
Name
Resident temp_1;
DROP Table temp_1;
next vsheet;
DROP Fields Temp;