Problem On Set Analysis

[infosense_devel]

Hello All,

Problem On Set Analysis,

O2C Link Table

Order	Line_id	Date	Item	Amount	Rec_Type
123	1	1/1/2016	ABC	3400	OrderDateRec
123	2	1/1/2016	XYZ	2200	OrderDateRec
124	3	1/1/2016	PQR	1100	OfflineDateRec
178	4	2/2/2016	SQD	1110	OrderDateRec
121	5	2/2/2016	SAW	1000	OfflineDateRec
111	6	2/2/2016	QWE	1111	OrderDateRec

1) I am using table O2C Link Table This expression is working fine means both set analysis amount is adding together regardless of selection.

=sum({<REC_TYPE = {OrderDateRec}>}( Amount))
+ Sum({<REC_TYPE = {OfflineData}>} Amount)

For E.g.

Date 1/1/2016 Amount is REC_TYPE = {OrderDateRec} + REC_TYPE = {OfflineData}

Is 3400+2200+1100 = 6700

It was working fine.

But,

As per my new requirement I have added new table as per below:

1)      XX_REC_TYPE_LOOKUP (Inline Table)

Rec_Type	XX_ORDER_RECORD_TYPE
OrderDateRec	AllSalesOrder
OfflineDateRec	OfflineData
BudgetRec	Budget
InvoiceDateRec	Invoice

We are joining this both table with Rec_type column.

When I have Joined XX_REC_TYPE_LOOKUP (Inline Table) table after that I am not getting desire result with existing expression.

=sum({<REC_TYPE = {OrderDateRec}>}( Amount))
+ Sum({<REC_TYPE = {OfflineData}>} Amount)

For e.g. I am selecting OrderDateRec It show the data for OrderDateRec not For Offline like,

1)

               Date 1/1/2016 Amount is REC_TYPE = {OrderDateRec} + REC_TYPE = {OfflineData}

               Is 3400+2200= 5600 (Not Including Offline Data)

     2)

         

               Date 1/1/2016 Amount is REC_TYPE = {OrderDateRec} + REC_TYPE = {OfflineData}

               Is  3400+2200= 6700 (Including Offline Data).

Thanks In Advance.

[vishsaggi]

Can you explain a little more please. Here when you join the Lookup table the order rec type -> All SalesOrders has only once record type -> OrderDateRec. So based on the selection of AllSalesOrders it will only give you OrderDateRec amount. That is how the association is formed. If you want the total based on date irrespective of RecordType or OrderRecType selections you can use this in pivot table like:

Dim: Date

Expr: = Sum({1< Rec_Type = {'OrderDateRec','OfflineDateRec'} >}Amount)

[sunny_talwar]

Would you be able to share the complete script here?

[hirenjadiya]

Hi,

I believe that , what Nagaraju has suggested should work. However you can try the below option also.

=sum({XX_ORDER_RECORD_TYPE = {AllSalesOrder}>}( Amount))
+ Sum({<XX_ORDER_RECORD_TYPE = {OfflineData}>} Amount)

[infosense_devel]

Hi,

in my script there too many expression is there that's so I don't want to change them all so make this entity XX_ORDER_RECORD_TYPE too translating the value of Rec_type for better understanding and meaningful name.

when I am joining this table mix set analysis is not working correctly like,

if I use only one expression =sum({<REC_TYPE = {OrderDateRec}>}( Amount)) it will give me right result if am  selecting "Allsalesorder" from list box named XX_ORDER_RECORD_TYPE.

But when I am adding two set analysis,

  =sum({<REC_TYPE = {OrderDateRec}>}( Amount))
+ Sum({<REC_TYPE = {OfflineData}>} Amount) in expression and make same selection that will only add "OrderDateRec" Amount.

[infosense_devel]

Hi,

There too many expression in my script so I don't want to change them all so prepare this XX_REC_TYPE_LOOKUP (Inline Table) Entity too translating Value only.

But not work with Mix set Analysis.

[tresesco]

Not sure if understood right. Try like:

  =sum({<REC_TYPE = {OrderDateRec}>}( Amount))
+ Sum({<XX_ORDER_RECORD_TYPE, REC_TYPE = {OfflineData}>} Amount)

[infosense_devel]

Hi,

Inline Table,

XX_REC_TYPE_LOOKUP:

LOAD * INLINE [

    "REC_TYPE", "XX_ORDER_RECORD_TYPE"

OrderDateRec, AllSalesOrder

PastDueRec, PastDueOrders

BacklogRec, BacklogOrders

BacklogRecHistory,BacklogRecHistory

BudgetRec,BudgetRec

GTNRec,GTNRec

InvoiceDateRec,InvoiceDateRec

OfflineData,OfflineData

OrderShipDateRec,OrderShipDateRec

ReturnDateRec,ReturnDateRec

];

O2C Link Table,

O2C_LINK_TABLE:
LOAD Distinct
    
  Order,
  Line_id,
         Date(Floor(CHANGE_DATE),'DD/MM/YYYY')  AS Common_Date,
  Item,
  Amount,
        'OrderDateRec' as REC_TYPE,
     FROM $(Qvc.Loader.v.QvdDirectory)XX_ORDER_CHANGES_SRC_F_V.QVD(qvd);
Concatenate
LOAD
Order,
  Line_id,
         Date(Floor(CHANGE_DATE),'DD/MM/YYYY')  AS Common_Date,
  Item,
  Amount,
     'OfflineData' as REC_TYPE,
     FROM $(Qvc.Loader.v.QvdDirectory)XXVEL_OM_OFFLINE_DATA_TBL_V.QVD(qvd);
STORE O2C_LINK_TABLE INTO $(Qvc.Loader.v.QvdDirectory)O2C_LINK_TABLE.QVD;

I want use list box of XX_ORDER_RECORD_TYPE instead of Rec_type.

and As per post in my all expression I have used set analysis on Rec_type don't want to change them all.

[infosense_devel]

Hi,

There too many expression in my script so I don't want to change them all so prepare this XX_REC_TYPE_LOOKUP (Inline Table) Entity too translating Value only.

[hirenjadiya]

Hi,

One more suggestion what I can suggest is in your chart create 3 expressions like -

     Expression 1: sum({<REC_TYPE = {OrderDateRec}>}( Amount)) Label it as OrderDateRecExpression

     Expression 2: Sum({<REC_TYPE = {OfflineData}>} Amount) Label it as OfflineDataExpression

     Expression 3: Column(OrderDateRecExpression)+Column(OfflineDataExpression)

Later, ho to Presentation Tab and Hide Column 1 and Column 2

Try, if this works out for you.