topic Re: Problem On Set Analysis in QlikView

Problem On Set Analysis

infosense_devel — Mon, 29 Aug 2016 14:22:39 GMT

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,

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

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

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

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

Thanks In Advance.

Re: Problem On Set Analysis

vishsaggi — Mon, 29 Aug 2016 15:15:12 GMT

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)

Re: Problem On Set Analysis

sunny_talwar — Mon, 29 Aug 2016 18:01:41 GMT

Would you be able to share the complete script here?

Re: Problem On Set Analysis

hirenjadiya — Mon, 29 Aug 2016 18:21:23 GMT

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)

Re: Problem On Set Analysis

infosense_devel — Tue, 30 Aug 2016 06:07:43 GMT

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.

Re: Problem On Set Analysis

infosense_devel — Tue, 30 Aug 2016 06:11:24 GMT

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.

Re: Problem On Set Analysis

tresB — Tue, 30 Aug 2016 06:33:13 GMT

Not sure if understood right. Try like:

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

Re: Problem On Set Analysis

infosense_devel — Tue, 30 Aug 2016 06:36:26 GMT

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.

Re: Problem On Set Analysis

infosense_devel — Tue, 30 Aug 2016 06:37:28 GMT

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.

Re: Problem On Set Analysis

hirenjadiya — Thu, 01 Sep 2016 17:37:28 GMT

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.