How to flag company appear in data source A and B ?

[paulyeo11]

Hi All

I have below load script , ot load 2 company raw data :-

sales:
LOAD
Date#(date_inv, 'D/M/YYYY') AS date,
LineSalesAmount,
profit_amt,
inv_no,
BRAND,
company,
SOURCE,
sbu,
ODSE

INLINE [date_inv, LineSalesAmount,profit_amt,inv_no,BRAND,company,sbu,ODSE,SOURCE
14/1/2018, 201801,4,1235 ,YASKAWA,JCS,AP_SBU,AP,COY1
14/1/2018, 201801,4,1235 ,YASKAWA,ABC,AP_SBU,AP,COY1

14/1/2018, 201801,4,1235 ,YASKAWA,JCS,AP_SBU,AP,COY2
14/1/2018, 201801,4,1235 ,YASKAWA,XYZ,AP_SBU,AP,COY2
];

May i know how to create new field = COY_SAME WITH 2 LABEL Yes or No , For filter company=JCS

Paul

[Saravanan_Desingh]

Are you looking something like this?

tab1:
LOAD *
	 ,If(company='JCS' And SOURCE='COY1', 'Yes', 'No') As COY1
	 ,If(company='JCS' And SOURCE='COY2', 'Yes', 'No') As COY2	 
;	  
LOAD * INLINE [
date_inv, LineSalesAmount,profit_amt,inv_no,BRAND,company,sbu,ODSE,SOURCE
14/1/2018, 201801,4,1235 ,YASKAWA,JCS,AP_SBU,AP,COY1
14/1/2018, 201801,4,1235 ,YASKAWA,ABC,AP_SBU,AP,COY1
14/1/2018, 201801,4,1235 ,YASKAWA,JCS,AP_SBU,AP,COY2
14/1/2018, 201801,4,1235 ,YASKAWA,XYZ,AP_SBU,AP,COY2
];

[paulyeo11]

Hi Sir

Thank you for your help.

Actually what I need is how to use expression at Table to filter our same customer appear at Coy A & Coy B. Hope you can advise me.

Paul

[Saravanan_Desingh]

Are you looking like this in the expression?

Only({<company=p({<SOURCE={'COY1'}>})*p({<SOURCE={'COY2'}>})>} company)

This pulls the company if it has COY1  & COY2. 

[paulyeo11 · Accepted solution]

Hi Saran

Thank you very much.

I try to add one more coy , but it does not work , pls let me know where go wrong :- 

Only({

<company=

p({

<SOURCE={'COY1'}>})

*

p({

<SOURCE={'COY2'}>})

*

p({

<SOURCE={'COY3'}>})>

} company)

Paul

[Saravanan_Desingh]

Please try this.

Only({<company=p({<SOURCE={'COY1'}>})*p({<company=p({<SOURCE={'COY3'}>})*p({<SOURCE={'COY2'}>})>})>} company)

[paulyeo11]

Hello

Thank you for your expression , your expression work fine. i have one more request , now i like to change my condition ,  how to flag only XYZ , meaning as long as one company appear at any of 3 SOURCE

INLINE [date_inv, LineSalesAmount,profit_amt,inv_no,BRAND,company,sbu,ODSE,SOURCE

14/1/2018, 201801,4,1235 ,YASKAWA,ABC,AP_SBU,AP,COY1

14/1/2018, 201801,4,1235 ,YASKAWA,XYZ,AP_SBU,AP,COY2

14/1/2018, 201801,4,1235 ,YASKAWA,JCS,AP_SBU,AP,COY3
14/1/2018, 201801,4,1235 ,YASKAWA,XYZ,AP_SBU,AP,COY3

];

Thank

Paul