Hi, I have the following script. ECode represents the customer ID and left join on two order tables. The order tables are divided into orders at the BU Segment level. One ECode may have placed orders in multiple BU Segments. I need a label based on the difference between CY and PY orders. For example, if there are orders in CY and no orders in PY, the label of the customer should be "with OR". Otherwise, the label should be "no OR". Since I have a large amount of data, writing if conditions on the frontend will result in slow display of chart. How can I write a flexible label in the script load to filter a BU Segment on the frontend and display the order label for that BU Segment? Also, how can I filter multiple BU Segments and display label from the total order difference for those BU Segments?
Script:
load distinct
ECODE,
BU,
BUSEGMENT
resident T_CompanyCDP_OR_FY23;
left join
LOAD
ECODE,
BU,
BUSEGMENT,
ORVALUE as CYOR,
TOVALUE as CYTO,
'CY'as FY
from CY_OrderDetail_List;
left join
LOAD
ECODE,
BU,
BUSEGMENT,
ORVALUE as PYOR,
TOVALUE as PYTO,
'PY'as FY
from PY_OrderDetail_List;
NoConcatenate
load
ECODE,
BU,
BUSEGMENT,
FY ,
CYOR,
CYTO,
PYOR,
PYTO,
if(CYOR-PYOR>0,'With OR','No OR') as Label
resident T_CompanyCDP_OR_FY23;
