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.
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;
Based on the U_Export create two different tables and rename according to your requirement ...if your not able to achieve share the same data set
Dear Avinash,
Is it correct
OCRT1:
collection,
cardcode,
U-Export
FROM
$(qvdpath)ORCT_*_*.qvd
(qvd) where U_Export='Y';
OCRT2:
collection,
cardcode,
U-Export
FROM
$(qvdpath)ORCT_*_*.qvd
(qvd) where U_Export='N';
Its correct but both the tables will be concatenated into one as they have common fields
use noconcatenate if you do not want it
Dear Avinash,
Please find the sample excel.
Dear Sasidhar,
It doesnt work when connecting with OCRD and CQ-RTSM tables.
Dear Avinash,
PFA sample app.
Thanks & Regards,
Joshua.
OCRT1:
LOAD cardcode,
[U-Export],
collection
FROM
[OCRT.xlsx]
(ooxml, embedded labels, table is Sheet1);
//where [U-Export]='Y';
Left Join(OCRT1)
LOAD cardcode,
customername,
[U-ATBeatcode] as Routcode_at,
[U-Beatcode] as Routcode_beat
FROM
[OCRD.xlsx]
(ooxml, embedded labels, table is Sheet1);
Beat:
noconcatenate Load * Resident OCRT1 Where [U-Export] ='Y';
ATBeat: QUALIFY *;
noconcatenate Load * Resident OCRT1 Where [U-Export] ='N';
DROP TABLE OCRT1;
Left Join(Beat)
LOAD
[U-Routcode] as Routcode_beat,
Beatname
FROM
[CQ-RTSM.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join(ATBeat)
LOAD
[U-Routcode] as ATBeat.Routcode_at,
Beatname as 123
FROM
[CQ-RTSM.xlsx]
(ooxml, embedded labels
, table is Sheet1);
Try the above code.. Rename the fields as you wish
Dear Varun,
I am getting duplication of records,
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
PFA for sample app.
Regards,
Joshua. !
Try like this
OCRD:
LOAD cardcode,
customername,
[U-ATBeatcode] as Routcode_at,
[U-Beatcode] 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;