Applymap based on condition

[ajsjoshua]

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.

[ajsjoshua]

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

[sasiparupudi1]

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;

[sasiparupudi1]

PFA

[ajsjoshua]

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

[ajsjoshua]

Hi Sasidhar,

PFA for OCRD table.

[boorgura · Accepted solution]

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;

[ajsjoshua]

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.