topic Re: Applymap based on condition in QlikView

Applymap based on condition

ajsjoshua — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Applymap based on condition

avinashelite — Tue, 10 Apr 2018 05:39:39 GMT

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

Re: Applymap based on condition

ajsjoshua — Tue, 10 Apr 2018 06:32:13 GMT

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';

Re: Applymap based on condition

sasiparupudi1 — Tue, 10 Apr 2018 06:41:36 GMT

Its correct but both the tables will be concatenated into one as they have common fields

use noconcatenate if you do not want it

Re: Applymap based on condition

ajsjoshua — Tue, 10 Apr 2018 06:44:03 GMT

Dear Avinash,

Please find the sample excel.

Re: Applymap based on condition

ajsjoshua — Tue, 10 Apr 2018 06:51:54 GMT

Dear Sasidhar,

It doesnt work when connecting with OCRD and CQ-RTSM tables.

Re: Applymap based on condition

ajsjoshua — Tue, 10 Apr 2018 06:59:42 GMT

Dear Avinash,

PFA sample app.

Thanks & Regards,

Joshua.

Re: Applymap based on condition

techvarun — Tue, 10 Apr 2018 07:42:28 GMT

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

Re: Applymap based on condition

ajsjoshua — Tue, 10 Apr 2018 08:21:03 GMT

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. !

Re: Applymap based on condition

sasiparupudi1 — Tue, 10 Apr 2018 11:32:02 GMT

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;

Re: Applymap based on condition

ajsjoshua — Tue, 10 Apr 2018 11:48:00 GMT

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

Re: Applymap based on condition

sasiparupudi1 — Tue, 10 Apr 2018 12:01:43 GMT

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;

Re: Applymap based on condition

sasiparupudi1 — Tue, 10 Apr 2018 12:03:41 GMT

PFA

Re: Applymap based on condition

ajsjoshua — Tue, 10 Apr 2018 12:14:36 GMT

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

Re: Applymap based on condition

ajsjoshua — Tue, 10 Apr 2018 13:10:08 GMT

Hi Sasidhar,

PFA for OCRD table.

Re: Applymap based on condition

boorgura — Tue, 10 Apr 2018 14:32:01 GMT

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;

Re: Applymap based on condition

ajsjoshua — Thu, 12 Apr 2018 05:52:37 GMT

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

the beat name sandy and reddy is not there.