Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have a table called OCRT which has a field U-Export.
If U-Export=Y
then the table OCRT has to be linked with the table OCRD by the link field Cardcode in both these tables.
then the OCRD table has to be linked to the table CQ-RTSM by the link fields U-ATBeatcode in OCRD with U-Routcode in CQ-RTSM
and
If U-Export=N
then the table OCRT has to be linked with the table OCRD by the link field Cardcode in both these tables.
then the OCRD table has to be linked to the table CQ-RTSM by the link fields U-Beatcode in OCRD with U-Routcode in CQ-RTSM
Any suggestion would be appreciated
Regards,
Joshua.
Hi Sasidhar,
Thanks for ur reply
I am not getting my expected output.
PFA for screen shot
my expected output is
customername Beatname collection
aaa sandy 2300
aaa barany 1500
bbb reddy 2311
bbb selv 763
ccc askar 123
ddd Richy 5400
Your OCRD Excel file is missing a key value U-BeatCode
OCRD:
LOAD cardcode,
customername,
[U-ATBeatcode] as Routcode_at,
IF(Len(Trim( [U-Beatcode])), [U-Beatcode],[U-ATBeatcode]) as Routcode_beat
FROM [OCRD.xlsx] (ooxml, embedded labels, table is Sheet1);
Left Join(OCRD)
LOAD [U-Routcode] as Routcode_beat,
Beatname
FROM [CQ-RTSM.xlsx] (ooxml, embedded labels, table is Sheet1);
OCRT1:
LOAD cardcode,
[U-Export],
collection
FROM [OCRT.xlsx] (ooxml, embedded labels, table is Sheet1)
;
Left Join(OCRT1)
Load *
Resident OCRD
;
drop table OCRD;
PFA
Hi Sasidhar,
According to ur script i am getting this output
customername | Beatname | collection |
aaa | barany | 1500 |
aaa | barany | 2300 |
bbb | selv | 763 |
bbb | selv | 2311 |
ccc | askar | 123 |
ddd | Richy | 5400 |
But my output should be
customername Beatname collection
aaa sandy 2300
aaa barany 1500
bbb reddy 2311
bbb selv 763
ccc askar 123
ddd Richy 5400
Based upon the field U-Export from the table OCRT
If U-Export=Y
then the table OCRT has to be linked with the table OCRD by the link field Cardcode in both these tables.
then the OCRD table has to be linked to the table CQ-RTSM by the link fields U-ATBeatcode in OCRD with U-Routcode in CQ-RTSM
and
If U-Export=N
then the table OCRT has to be linked with the table OCRD by the link field Cardcode in both these tables.
then the OCRD table has to be linked to the table CQ-RTSM by the link fields U-Beatcode in OCRD with U-Routcode in CQ-RTSM
Hi Sasidhar,
PFA for OCRD table.
Please try this code:
OCRT1:
LOAD cardcode,
[U-Export] as e1,
collection
FROM
[OCRT.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
OCRD:
NoConcatenate
LOAD cardcode,
customername,
[U-ATBeatcode] as Routcode_at,
[U-Beatcode] as Routcode_beat,
'Y' as e1
FROM
[OCRD.xlsx]
(ooxml, embedded labels, table is Sheet1);
left Join(OCRD)
LOAD [U-Routcode] as Routcode_at,
//[U-Routcode] as Routcode_beat,
Beatname
FROM
[CQ-RTSM.xlsx]
(ooxml, embedded labels, table is Sheet1);
OCRD2:
NoConcatenate
LOAD cardcode,
customername,
[U-ATBeatcode] as Routcode_at,
[U-Beatcode] as Routcode_beat,
'N' as e1
FROM
[OCRD.xlsx]
(ooxml, embedded labels, table is Sheet1);
left Join(OCRD2)
LOAD //[U-Routcode] as Routcode_at,
[U-Routcode] as Routcode_beat,
Beatname
FROM
[CQ-RTSM.xlsx]
(ooxml, embedded labels, table is Sheet1);
Concatenate(OCRD)
LOAD * Resident OCRD2;
DROP Table OCRD2;
Left Join(OCRT1)
LOAD * Resident OCRD;
DROP Table OCRD;
Hi Sasidhar,
It should be
aaa sandy 2300
aaa barany 1500
bbb reddy 2311
bbb selv 763
but according to ur script
aaa barany 2300
aaa barany 1500
bbb selv 763
bbb selv 763
the beat name sandy and reddy is not there.